5

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!

2
  • You might have more luck over at the Stack Exchange Database Administrators site.
    – Chris_O
    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
    Oct 11, 2011 at 22:05

2 Answers 2

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.

3
  • When I changed the == to = and corrected the spelling of 'capabilities' this worked a treat. Thanks EAMann! As I'm using this in phpmyadmin I dont need the $wpdb-> to specify the table prefix. But for anyone who is coding this into php its definitely the way to go!
    – Gyro
    Oct 11, 2011 at 20:52
  • Sorry, I wrote this quickly while debugging a bunch of .Net equality operations ... == works better in that world, so I put it in by mistake. Everything's corrected above.
    – EAMann
    Oct 11, 2011 at 22:04
  • This was very helpful for me to find users that are woocommerce customers since that information is set in the wp_capabilities field. Thanks!
    – Denoteone
    Aug 19, 2014 at 7:37
-1

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.

Your Answer

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

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