How to get users who belong to a WordPress Multisi

Question

I need to get all the users who have joined (are members) of a site (blog) in WordPress multisite. To complicate it, I am doing this outside of WordPress and don't have access to internal Wordpress functions, so need to construct the SQL directly.

In English the SQL would break down as "get an array of user IDs of users that are members of site x" (where site relates to one of the WordPress Multisite sites).

I've tried going through WordPress code to understand, but struggling with the above.

I don't need the PHP, I can work that out, just an example SQL statement.

Many thanks!

Answer 1

1. select * from wp_blogs

From the output of the command note down the blog_id you want the users of. For eg: say you are wanting the users of the site with blog_id = 2 , next run the following query.

2. select * from wp_users u join wp_usermeta um on u.id=um.user_id where um.meta_key="wp_2_capabilities"

Answer 2

Thanks for this code, just what I needed!

If it helps anyone else, I also extended it a bit to get how many people have a given role, e.g. a custom role teacher was used below:

select * from wp_users u join wp_usermeta um on u.id=um.user_id where um.meta_key="wp_2_capabilities" AND um.meta_value LIKE '%teacher%'

This of course requires other roles not to contain the word teacher (e.g. maybe there's a role ex-teacher which it would also pick up). That was the case for me so I ran two queries and subtracted the two numbers.

If anyone knows how to do it with one query that would be nice?