Query Wordpress database by registered date and role

Question

I would like to get the display name and email address for all users who have registered to my multisite today with the role of subscriber. It needs to be an SQL query as it's used outside of the WP files.

I've gathered I need the following:

Table: wp_users Columns: user_registered, user_email, display_name

Table: wp_usermeta Columns: wp_6_capabilities = a:1:{s:10:"subscriber";b:1;}

I'm guessing I need to get the user ID's from wp_users of all users who have subscribed today, then check against wp_usermeta to narrow those results down by the user role. Then I need to go back to wp_users and get the email and display name.

I've been trying to write an SQL query to do this for a while and can't seem to come up with anything.

Any help is appreciated.

Thanks.

Answer 1

I'm no SQL expert, but I think it would look something like this:

SELECT * FROM mydatabase.wp_users 
INNER JOIN mydatabase.wp_usermeta 
ON (wp_users.ID = wp_usermeta.user_id) 
WHERE 1=1 
AND wp_users.user_registered > '2014-03-15 00:00:00' 
AND wp_users.user_registered < '2014-03-16 00:00:00'
AND ( (wp_usermeta.meta_key = 'wp_capabilities' 
AND CAST(wp_usermeta.meta_value AS CHAR) 
LIKE '%\"subscriber\"%') ) 
ORDER BY user_registered ASC ;