Geek Answers Handbook

The right mySql command to get meta information?

Wordpress is a good example of a web application that uses a table for user information and then a meta search table for user data. The only problem is that the only way to get a complete list of meta-information for a list of users is to build the sql statement “manually” - either hardcoded or using PHP.

The user table looks something like this:

wp_users table

  ID | user_login | user_email | user_pass | date_registered
 ===================================================
  1 |  me |  me@me1.com | f239j283r |  2011-01-01

wp_usermeta strong table>

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

I know that I can do something like this (manually or using php) to achieve the result that 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)

which gives 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 that mySql also has a GROUP_CONCAT property, so I feel that there is a better way. It will 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 a result similar to the result from the first sql statement, with a more dynamic sql statement like the second?

Edit

Doug proposed an interesting solution, possibly using information_schema. I had problems with work, so I published a dump of two tables for those who want to test their SQL :) http://pastebin.com/w0jkxnws

+4
source share
4 answers

Is this what you are looking for? These are still 3 statements, but contrary to my previous statement to the contrary, there should not be a lot of preparation 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;
+1
source

Usually this is done by running 2 queries on the database - first to get a user record, the second for properties.

0
source

try it

 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;
0
source

You can 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
0
source

More articles:


All Articles