SQL Query to Get Users With Role Subscriber

1 Views

I haven’t got any luck with my SQL query to get users with role subscriber: 

SELECT 
      ID, 
      display_name 
FROM 'wp_users' 
INNER JOIN 'wp_usermeta' ON 'wp_users'.ID = 'wp_usermeta'.user_id 
WHERE 'wp_usermeta'.meta_key = 'wp_capabilities' 
AND ('wp_usermeta'.meta_value LIKE 'subscriber') ORDER BY display_name

Can any body help me?

Post Views: 1

Related posts

Leave a Reply

7 comments

  1. I have got the answer to my question:

    SELECT wp_users.ID, wp_users.user_nicename 
FROM wp_users INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
WHERE wp_usermeta.meta_key = 'wp_capabilities' 
AND wp_usermeta.meta_value LIKE '%subscriber%' 
ORDER BY wp_users.user_nicename

    If anybody struggling with the same issue please use my SQL query above.

  2. Here’s a slight variant of @qqruza’s answer that includes the user’s email and role and returns users for all roles.

    SELECT wp_users.ID, wp_users.user_nicename, wp_users.user_email, wp_usermeta.meta_value 
     FROM wp_users 
     JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
     WHERE wp_usermeta.meta_key = 'wp_capabilities'
     ORDER BY wp_users.user_nicename

    If you have a WordPress multisite installation, to get the roles for all child sites, use:

    SELECT wp_users.ID, wp_users.user_nicename, wp_users.user_email, wp_usermeta.meta_key, wp_usermeta.meta_value 
     FROM wp_users 
     JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
     WHERE wp_usermeta.meta_key LIKE 'wp_%capabilities'
     ORDER BY wp_users.user_nicename

    Of course, you’ll need to look at the wp_usermeta.meta_key value to determine which child site (blog) the record applies to.

  3. This Worked for me : 

         SELECT a.ID, a.user_login  
     FROM wp_users a
     JOIN wp_usermeta b ON a.ID = b.user_id 
     WHERE b.meta_key = 'wp_capabilities' and b.meta_value like '%contributor%'
     ORDER BY a.user_nicename

  4. I’m not familiar with the WordPress internal structure, but I can tell you that your LIKE statement is probably wrong. There’s no implicit/automatic wildcards in the LIKE argument, so what you have (with no wildcards) is basically the same as using =. Ie. you probably want:

    'wp_usermeta'.meta_value LIKE '%subscriber%'

  6. If you want to grab other metainfo you can use subqueries, just remember the more subqueries, the slower the overall query is going to be.

    SELECT 
    ID, 
    user_email,
    (SELECT `meta_value` FROM wp_usermeta WHERE wp_usermeta.user_id=wp_users.ID AND `wp_usermeta`.`meta_key` = 'first_name') as first_name,
    (SELECT `meta_value` FROM wp_usermeta WHERE wp_usermeta.user_id=wp_users.ID AND `wp_usermeta`.`meta_key` = 'last_name') as last_name,
    user_registered
FROM wp_users
JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID
WHERE `meta_value` LIKE '%subscriber%';

  7. SELECT id,
       user_email,
       (SELECT meta_value
        FROM   wp_usermeta
        WHERE  wp_usermeta.user_id = wp_users.id
               AND wp_usermeta.meta_key = 'first_name') AS first_name,
       (SELECT meta_value
        FROM   wp_usermeta
        WHERE  wp_usermeta.user_id = wp_users.id
               AND wp_usermeta.meta_key = 'last_name')  AS last_name,
       user_registered
FROM   wp_users
       JOIN wp_usermeta
         ON wp_usermeta.user_id = wp_users.id
WHERE meta_value  LIKE '%subscriber%'