Right mySql command to get meta information?

Wordpress is a good example of a web application that uses a table for user info, and then a meta lookup table for user data. The only problem is that the only way I know of to get a complete list of meta information for a list of users is to build the sql statement "manually" - either hard coded or with the help of PHP.

The user table looks something like this:

wp_users table

ID|user_login|user_email|user_pass|date_registered
=====================开发者_C百科=============================
 1|   me     |me@me1.com|f239j283r|   2011-01-01

wp_usermeta table

umeta_id|user_id|meta_key|meta_value
====================================
   1    |   1   | phone  | 123-4567
   1    |   1   | fname  | john
   1    |   1   | lname  | doe

I know I can do something like this (manually or with php) to achieve the result of what I want:

select *
from wp_users
left join wp_usermeta as phone on (ID = user_id) AND (meta_key = phone)
left join wp_usermeta as fname on (ID = user_id) AND (meta_key = fname)
left join wp_usermeta as lname on (ID = user_id) AND (meta_key = lname)

that yields something like this:

ID|user_login|user_email|user_pass|date_registered|phone   |fname|lname
=================================================================+++===
 1|   me     |me@me1.com|f239j283r|   2011-01-01  |123-4567|john |doe

I know mySql also has the GROUP_CONCAT thing, which is why I feel like there is a better way. That would look something like this:

select *, group_concat(meta_value) as all_meta
from wp_users
left join wp_usermeta on ID = user_id
group by wp_users.ID

So is there a way to get the result similar to that from the first sql statement with a more dynamic sql statement like the second one?

Edit

Doug has proposed an interesting solution, possibly using information_schema. I was having trouble getting that to work so I've posted a dump of the two tables for anyone who wants to test their SQL :) http://pastebin.com/w0jkxnws

---

Is this what you're looking for? It's still 3 statements, but, contrary to my previous statement to the contrary, there shouldn't be much prep cost.

set group_concat_max_len = 2048;
SELECT CONCAT('SELECT u.id, u.user_login, ', GROUP_CONCAT(concat('
    (SELECT meta_value FROM wp_usermeta WHERE user_id = u.id AND meta_key = "', um.meta_key, '") `', um.meta_key, '`') SEPARATOR ", "), '
    FROM wp_users u ') FROM (SELECT DISTINCT meta_key FROM wp_usermeta) um INTO @sql;

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

---

Usually this is done by running 2 queries against the database – first to fetch user record, second for properties.

---

Try this

select *
from wp_users
left join wp_usermeta as fname on (ID = user_id)
where meta_key in ('fname','lname','phone')
group by ID, meta_key;

---

You could try something like this:

SELECT
  u.*,
  MIN(CASE m.meta_key WHEN 'phone' THEN m.meta_value END) AS phone,
  MIN(CASE m.meta_key WHEN 'fname' THEN m.meta_value END) AS fname,
  MIN(CASE m.meta_key WHEN 'lname' THEN m.meta_value END) AS lname
FROM wp_users u
  LEFT JOIN wp_usermeta m ON u.ID = m.user_id
    AND m.meta_key IN ('phone', 'fname', 'lname')
GROUP BY u.ID