SQL select of users by metadata

Hi I'm trying to select users with a particular role only, using the following sql statement...

SELECT DISTINCT ID, u.user_login, u.user_nicename, u.user_email
FROM wp_users u, wp_usermeta m
WHERE m.meta_key = 'wp_capabilities'
AND m.meta_value LIKE '%supplier%'
ORDER BY u.user_registered

However, it reurns the whole table. What am I doing wrong?

PS this needs to be a SQL select as I'm doing this in myphpadmin to export the data to csv.

Thanks in advance!

Hi I'm trying to select users with a particular role only, using the following sql statement...

SELECT DISTINCT ID, u.user_login, u.user_nicename, u.user_email
FROM wp_users u, wp_usermeta m
WHERE m.meta_key = 'wp_capabilities'
AND m.meta_value LIKE '%supplier%'
ORDER BY u.user_registered

However, it reurns the whole table. What am I doing wrong?

PS this needs to be a SQL select as I'm doing this in myphpadmin to export the data to csv.

Thanks in advance!

• sql

Share Improve this question asked Oct 11, 2011 at 14:02 GyroGyro 6511 gold badge11 silver badge66 bronze badges 2

• You might have more luck over at the Stack Exchange Database Administrators site. – Chris_O Commented Oct 11, 2011 at 15:41
• 1 @Chris_O I disagree. While this is pertinent to generic DB operations, it also does cover bits of the WordPress API as shown by kaiser's answer below. I think it should stay. – EAMann Commented Oct 11, 2011 at 22:05

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 6

Double-check your SQL syntax. It sounds like you want to do a JOIN ... But you're not building the query correctly.

It should be something more like:

SELECT u.ID, u.user_login, u.user_nicename, u.user_email
FROM $wpdb->users u
INNER JOIN $wpdb->usermeta m ON m.user_id = u.ID
WHERE m.meta_key = 'wp_capabilities'
AND m.meta_value LIKE '%supplier%'
ORDER BY u.user_registered

You have to tell the query how you're relating the user meta to the user.

Also note that I'm using $wpdb-> rather than wp_. This is important if you ever expect to use this query in a plugin on a site with a database prefix other than "wp." If you're running things directly in SQL, though, you should switch back.

There's a (query-)class built into core:

new WP_User_Query;

// Example
$all_subscribers = new WP_User_Query( array( 'role' => 'subscriber' ) );

This then allows looping through the user objects:

// Example
foreach ( $all_subscribers as $subscriber )
{
    echo $subscriber->display_name;

    // Check object:
    var_dump( $subscriber );
}

For further examples, or querying users by other fields, please consult the codex.