Trying to get user id and user meta data from WordPress database

Question

I am trying to get a list of all users in WordPress getting the user ID from the user table and some additional meta information from the usermeta table for the corresponding user. I am trying the below code but it's not working. I think I am going wrong in the INNER JOIN part of the statement but I don't know how to correct it.

$sql = "
SELECT {$wpdb->users}.ID,{$wpdb->usermeta}.meta_value
FROM {$wpdb->users},{$wpdb->usermeta} 
INNER JOIN {$wpdb->usermeta} ON ({$wpdb->users}.ID = {$wpdb->usermeta}.user_id)
WHERE 1=1 AND {$wpdb->usermeta}.meta_key = 'first_name' OR {$wpdb->usermeta}.meta_key = 'last_name' OR {$wpdb->usermeta}.meta_key = 'dev_capabilities'         
";

$users = $wpdb->get_results($sql);

p.s. I realise there's easier ways to get this information in WordPress by using the get_users() function but because this is in a particular action I can't use this method.

JNevill JNevill · Accepted Answer · 2017-06-08T15:06:16

You are close. You are actually, accidentally, joining in your usermeta data in twice. Once as as an implicit cross join with the , and once again as an explicit INNER JOIN. Instead:

$sql = "
SELECT {$wpdb->users}.ID,{$wpdb->usermeta}.meta_value
FROM {$wpdb->users} INNER JOIN {$wpdb->usermeta} ON ({$wpdb->users}.ID = {$wpdb->usermeta}.user_id)
WHERE 1=1 AND {$wpdb->usermeta}.meta_key = 'first_name' OR {$wpdb->usermeta}.meta_key = 'last_name' OR {$wpdb->usermeta}.meta_key = 'dev_capabilities'         
";

$users = $wpdb->get_results($sql);

To get first and last name as well as dev capabilities in the same record (and, I assume, in the same array that $wpdb outputs) you can inner join to that meta table three times. Sticking each one of those into a subquery makes it easy to parse what's happening:

$sql = "
SELECT {$wpdb->users}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, devcapabilities.meta_value as dev_capabilities
FROM {$wpdb->users} 
    INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->users}.ID = firstname.user_id
    INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->users}.ID = lastname.user_id
    INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'dev_capabilities') as devcapabilities ON {$wpdb->users}.ID = devcapabilities.user_id
";

$users = $wpdb->get_results($sql);

Trying to get user id and user meta data from WordPress database

1 Answers