2

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.com
abc  example.com
ggh  example.com

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?

3 Answers 3

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
2
  • 1
    can you please give me a direct sql query, that would be very helpful..
    – NEO
    Dec 19, 2013 at 17:58
  • and how should i use this code?
    – NEO
    Dec 19, 2013 at 17:59
4

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.com    administrator
user          blog1.com    editor
webmaster     blog2.com    administrator
0

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.