SQL Query to Get Users With Role Subscriber

Question

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?

Solution 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.

Solution 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

Solution 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

Solution 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%'

Solution 5

You can use:

$user_query = new WP_User_Query( array( 'role' => 'Subscriber' ) );

View more solutions

Comments

[Savan Dholu]

It's Working for my side

[Devner]

Hi, the role returned is something like this role: "a:1:{s:13:"administrator";b:1;}". How can one go about extracting only the administrator part from the value?

[Martin_W]

That is a PHP serialized value. You could use some SQL string manipulation to search for {s:13:"administrator";b:. But the most reliable way of parsing it is to use PHP to deserialize. Of course, that's not a SQL-only solution.