Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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!

share|improve this question
    
You might have more luck over at the Stack Exchange Database Administrators site. –  Chris_O Oct 11 '11 at 15:41
    
@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 '11 at 22:05

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 at 22:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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