1

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.

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.