Make Meta_Key column headers

Question

totally new to MySQL and am trying to extract some data - I was expecting this to be a relatively simple task but I didnt realise that the "format" of the data extract wouldnt have the column headings i expected. I've done some research and think I'm very close to the answer, provided here:

SQL - How to transpose?

However, when writing my query I'm getting a syntax error.

So here is the code I've applied (using a little common sense to work out my specific values, although may have totally missed the mark!)

SELECT wp_usermeta.User_ID
   MAX(CASE WHEN wp_usermeta.meta_key = 'nickname' THEN wp_usermeta.meta_value ELSE NULL END) AS 'nickname',
   MAX(CASE WHEN wp_usermeta.meta_key = 'sex' THEN wp_usermeta.meta_value ELSE NULL END) AS 'sex'
FROM wp_usermeta
GROUP BY wp_usermeta.User_ID

The error I'm getting is a #1064 advising to check the syntax on line 2 near 'MAX(CASE WHEN wp_usermeta.meta_key = 'nickname' THEN wp_usermeta.meta_value ELSE'

I'm sure this is a simple syntax error but i can't work it out!

Many thanks,

Stuart

Assuming you mean at the end of line 3 so it would be: MAX(CASE WHEN wp_usermeta.meta_key = 'sex' THEN wp_usermeta.meta_value ELSE NULL END) AS 'sex', — user3307899, Feb 13 '14 at 21:39
@user3307899 try this `SELECT wp_usermeta.User_ID, MAX(CASE WHEN wp_usermeta.meta_key = 'nickname' THEN wp_usermeta.meta_value ELSE NULL END) AS 'nickname', MAX(CASE WHEN wp_usermeta.meta_key = 'sex' THEN wp_usermeta.meta_value ELSE NULL END) AS 'sex' FROM wp_usermeta GROUP BY wp_usermeta.User_ID` — M Khalid Junaid, Feb 13 '14 at 21:43
That's got it! One comma, curses, that had me cross eyed for days! Thank you! Now to work out how to extract the returned data... — user3307899, Feb 13 '14 at 21:49

score 0 · Answer 1 · answered Feb 13 '14 at 22:03

0

In wordpress you can use WPDB class to retrieve results from raw query

global $wpdb;
$results=$wpdb->get_results( "SELECT wp_usermeta.User_ID,
   MAX(CASE WHEN wp_usermeta.meta_key = 'nickname' THEN wp_usermeta.meta_value ELSE NULL END) AS 'nickname',
   MAX(CASE WHEN wp_usermeta.meta_key = 'sex' THEN wp_usermeta.meta_value ELSE NULL END) AS 'sex'
FROM wp_usermeta
GROUP BY wp_usermeta.User_ID" );
if(!empty($results)){
foreach($results as $r){
echo $r->User_ID ."<br />";
echo $r->nickname ."<br />";
echo $r->sex ."<br />";
}
}

Wordpress Class WPDB

answered Feb 13 '14 at 22:03

M Khalid Junaid

63,861
10
90
118

Thanks for that Khalid - am happy enough with the missing comma answer above - it's exactly what i needed. – user3307899 Feb 13 '14 at 22:07
@user3307899 if above answer is helpfull then read [Accepting Answers: How does it work?](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) – M Khalid Junaid Feb 14 '14 at 10:50

score 0 · Answer 2 · answered Feb 13 '14 at 22:12

If you write your SELECTs this way, then that problem can never occur...

SELECT m.User_ID
     , MAX(CASE WHEN m.meta_key = 'nickname' THEN m.meta_value END) nickname
     , MAX(CASE WHEN m.meta_key = 'sex' THEN m.meta_value END) sex
  FROM wp_usermeta m
 GROUP 
    BY m.User_ID

Make Meta_Key column headers

2 Answers2