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

by

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

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

Leave a Comment