multisite - SQL Query to Get list of all users along with their blogs

How to query for a list of users along with the blogs in which they are member of in a multi site wordpress installation.

For example, I need the results as below

user domain
abc  xyz
abc  example
ggh  example

I tried the below query, but its not listing all the domains..

SELECT DISTINCT a.user_login, a.user_email, c.domain
FROM wp_users a
JOIN wp_usermeta b ON a.id = b.user_id
JOIN wp_blogs c ON b.meta_value = c.site_id
LIMIT 0 , 100

I am not sure how the network/multi site works.. can anyone please tell me how to get the result?

How to query for a list of users along with the blogs in which they are member of in a multi site wordpress installation.

For example, I need the results as below

user domain
abc  xyz
abc  example
ggh  example

I tried the below query, but its not listing all the domains..

SELECT DISTINCT a.user_login, a.user_email, c.domain
FROM wp_users a
JOIN wp_usermeta b ON a.id = b.user_id
JOIN wp_blogs c ON b.meta_value = c.site_id
LIMIT 0 , 100

I am not sure how the network/multi site works.. can anyone please tell me how to get the result?

• multisite
• mysql

Share Improve this question asked Dec 19, 2013 at 17:16 NEONEO 13322 silver badges77 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 3

You can use get_blogs_of_user() to get all the blogs of a given user.

So to get a list sorted by the users:

global $wpdb;
$blogs = array();
$user_ids = $wpdb->get_col( 'SELECT ID FROM $wpdb->users' );
foreach( $user_ids as $user_id ) {
    $blogs[$user_id] = get_blogs_of_user( $user_id );
}
// you can use var_dump( $blogs ); to see what's in the $blogs array

Needing the same thing, I managed to find the key data in the wp_usermeta tables. This worked for me:

SELECT u.user_login, b.domain,
  SUBSTRING_INDEX(SUBSTRING_INDEX(um.meta_value,'"', 2), '"', -1) as role
FROM wp_users u
JOIN wp_usermeta um ON u.ID = um.user_id
JOIN wp_blogs b ON b.blog_id = SUBSTRING(um.meta_key, 4, LENGTH(um.meta_key)-16)
WHERE um.meta_key LIKE 'wp_%_capabilities'

Results:

user_login    domain       role
============  ============ ================
webmaster     blog1    administrator
user          blog1    editor
webmaster     blog2    administrator

Can't leave a comment but the response from Wraezor Sharp worked pretty well for me. I needed user email addresses, our multisite instance was all on the same domain name, and I only wanted admins and not subscribers. This is what I came up with:

SELECT
  u.user_login
  , u.user_email
  , concat('https://', b.domain, b.path) as site
  , SUBSTRING_INDEX(SUBSTRING_INDEX(um.meta_value,'"', 2), '"', -1) as role
FROM
  wp_users u
  JOIN wp_usermeta um ON u.ID = um.user_id
  JOIN wp_blogs b ON b.blog_id = SUBSTRING(um.meta_key, 4, LENGTH(um.meta_key)-16)
WHERE
  um.meta_key LIKE 'wp_%_capabilities'
  AND SUBSTRING_INDEX(SUBSTRING_INDEX(um.meta_value,'"', 2), '"', -1) IN ('administrator', 'editor')
ORDER BY
  site asc
;