Using GROUP CONCAT in my-sql query with wp_usermeta table

Question

I have a query that is meant to return user details from Wordpress tables. It would be fine if I was just SELECTing column names that I want, but within Wordpress there is a usermeta table which has 2 columns - 1 called metakey and 1 called meta value.

I want to get certain bits of user info from meta keys such as first_name and last_name, but they are all within the same column - metavalue.

Here is what I have:

    $allquery="SELECT $comma_separated, wp_usermeta.meta_value, wp_usermeta.meta_key, 
//comma_seperated are a list of values to search for seperate by ",". this is name, email
    GROUP_CONCAT(
        wp_usermeta.meta_value
        ORDER BY wp_usermeta.meta_key
    ) AS name
    FROM wp_users
    LEFT JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id
    WHERE  (wp_usermeta.meta_key = 'first_name'
        OR wp_usermeta.meta_key = 'last_name')
        AND wp_users.user_login = '$spec_user'
    GROUP BY wp_users.ID";

    $names = array();
    $allresult=mysql_query($allquery) or die(mysql_error()); 

    while($rows=mysql_fetch_array($allresult)){  

        $names[] = $rows['name']; //name is from the group_concat in query
        $emails[] = $rows['user_email']; 
    }

The problem is, this just returns a single row. I think it could be something to do with the line:

AND wp_users.user_login = '$spec_user'

Where $spec_user is a user entered value.

What I want ideally is all rows returned where the user_login column equals what the user has entered. Not just from that table, but from the usermeta table.

Answer 1

How about a Pivot Query?

This would return rows that have first_name,last_name,user_login. You could then add whatever condition you wanted using a WHERE clause.

            SELECT

                    MAX(CASE WHEN wp_usermeta.meta_key = 'first_name' then wp_usermeta.meta_value ELSE NULL END) as first_name,
                    MAX(CASE WHEN wp_usermeta.meta_key = 'last_name' then wp_usermeta.meta_value ELSE NULL END) as last_name,
                    wp_users.user_login

                    FROM wp_users

                    LEFT JOIN wp_usermeta
                    ON wp_users.ID = wp_usermeta.user_id

                    GROUP BY wp_users.user_login