Generate a user list per site to communicate upgrade plans

6 comments

1. This should do the trick. Read along with the comments for some explanation.

   // get users with specified roles -- this can go in functions
   function get_users_with_role( $roles ) {
       global $wpdb;
       if ( ! is_array( $roles ) )
           $roles = array_walk( explode( ",", $roles ), 'trim' );
       $sql = '
           SELECT  ID 
           FROM        ' . $wpdb->users . ' INNER JOIN ' . $wpdb->usermeta . '
           ON          ' . $wpdb->users . '.ID             =       ' . $wpdb->usermeta . '.user_id
           WHERE       ' . $wpdb->usermeta . '.meta_key        =       '' . $wpdb->prefix . 'capabilities'
           AND     (
       ';
       $i = 1;
       foreach ( $roles as $role ) {
           $sql .= ' ' . $wpdb->usermeta . '.meta_value    LIKE    '%"' . $role . '"%' ';
           if ( $i < count( $roles ) ) $sql .= ' OR ';
           $i++;
       }
       $sql .= ' ) ';
       $sql .= ' ORDER BY display_name ';
       $userIDs = $wpdb->get_col( $sql );
       return $userIDs;
   }
   
   ////// everything else could go in a custom page template just for viewing temporarily.
   
   // poll database for users we need, using custom function (listed above)
   $editors_and_admins = get_users_with_role(array('editor', 'administrator'));
   
   // get user objects
   $editors_and_admins = get_users(array('include' => $editors_and_admins);
   
   echo '<table>';
   // spit out as table - not sure what output you need. could easily create CSV by modifying this
   foreach($editors_and_admins as $constituent){
       echo '<tr>'
       // get name
       echo '<td>'.get_the_author_meta('first_name', $constituent->ID).' '.get_the_author_meta('last_name', $constituent->ID).'</td>';
       // get email
       echo '<td><a href="mailto:'.$constituent->user_email'">'.$constituent->user_email.'</a></td>';
       // get URL
       echo '<td><a href="'.$constituent->user_url.'">'.$constituent->user_url.'</a></td>';
       echo '</tr>';
   }
   echo '</table>';
   

2. There’s get_users() — you could use it in conjunction with the results of a $wpdb query along the lines of $wpdb->get_col( "SELECT blog_id FROM {$wpdb->blogs}" );

   function tell_all() {
       global $wpdb;
       $all_sites = $wpdb->get_col( "SELECT blog_id FROM {$wpdb->blogs}" );
       $list = array();
       foreach( $all_sites as $site ) {
           $args = array(
               'blog_id' => $site,
               'fields' => 'user_email',
               'role' => 'Editor',
           );
           $url = get_blogaddress_by_id( $site );
           $list[$url] = array();
           $editors = get_users( $args );
           $args['role'] = 'Administrator';
           $administrators = get_users( $args );
           $users = array_merge( $editors, $administrators );
           foreach( $users as $user ) {
               $list[$url][] = $user;
           }
   
       }
   }
   

   If I’ve got all that right, then at the end, $list should be an array of the format

   $list = array(
       'site-1' => array( 
           'email-1',
           'email-2',
       ),
       'site-2' => array(
           'email-1',
           'email-3',
       ),
       .
       .
       .
   );
   

   References

   Codex:

   • get_users()
   • get_blogaddress_by_id()

3. Make backups before attempting anything.

   Phpmyadmin, SQL tab, then: “Run SQL query/queries on database”:

   SELECT COUNT(*) AS Rows, user_login, user_email, user_nicename, user_url FROM wp_users GROUP BY user_login ORDER BY user_login
   

   OR if you’re not comfortable with SQL:

   

   Select wp_users

   “Check All”

   Query results operations

   Export

   Then select MS Excel or CSV and further edit until you’re left with user login, nicename, email, and user_url.

   That should be sufficient enough to make contact

4. 

   If you not want to use any script then you can use wordpress plugin Export Users to CSV.This can export ALL user data and meta data, and that works!
   You can even export the users by role and registration date range.

   Features:

   1. Exports all users fields
   2. Exports users meta
   3. Exports users by role
   4. Exports users by date range

5. I have no idea if there are any plugin allowing this, but if you don’t mind using a bit of SQL you can extract it this way:

   SELECT u.ID, u.user_login, u.user_email, u.user_registered, u.display_name, b.path 
   FROM wp_users u
   LEFT JOIN wp_usermeta um ON um.user_id = u.ID
   LEFT JOIN wp_blogs b ON um.meta_key LIKE CONCAT_WS('_', 'wp', b.blog_id, 'capabilities')
   WHERE um.meta_value LIKE '%"administrator"%' OR um.meta_value LIKE '%"editor"%';
   

   This will produce the following resultset:

   +----+------------+---------------------+---------------------+--------------+----------------+
   | ID | user_login |     user_email      |   user_registered   | display_name |      path      |
   +----+------------+---------------------+---------------------+--------------+----------------+
   |  1 | jdoe       | johndoe@example.com | 2014-12-02 10:46:26 | John Doe     | /subsite-path/ |
   +----+------------+---------------------+---------------------+--------------+----------------+
   

   where ID, user_login, user_email, user_registered and display_name are user’s details while path is the subsites URL (you might want to select domain instead of path if your network install is domain based and not directory based)

   Note that this will produce one row per site where the user is registered, which means that if you have a user registered as an admin on one site and as an editor on the other, the query will produce 2 rows for this user.

   Using GROUP_CONCAT you can concatenate all these rows into one:

   SELECT u.ID, u.user_login, u.user_email, u.user_registered, u.display_name, GROUP_CONCAT(b.path ORDER BY b.path SEPARATOR 'n') 
   FROM wp_users u
   LEFT JOIN wp_usermeta um ON um.user_id = u.ID
   LEFT JOIN wp_blogs b ON um.meta_key LIKE CONCAT_WS('_', 'wp', b.blog_id, 'capabilities')
   WHERE um.meta_value LIKE '%"administrator"%' OR um.meta_value LIKE '%"editor"%'
   GROUP BY u.ID;
   

   This will produce the following resultset:

   +----+------------+---------------------+---------------------+--------------+-----------------+
   | ID | user_login |     user_email      |   user_registered   | display_name |       path      |
   +----+------------+---------------------+---------------------+--------------+-----------------+
   |  1 | jdoe       | johndoe@example.com | 2014-12-02 10:46:26 | John Doe     | /subsite1-path/ |
   |    |            |                     |                     |              | /subsite2-path/ |
   +----+------------+---------------------+---------------------+--------------+-----------------+
   

6. Initiate console/terminal

   mysql -u username -p
   password
   
   USE wpdatabasename;
   
   SELECT 
       `ID` ,
       `user_login` , 
       `user_nicename` , 
       `user_email` , 
       `user_url` , 
       `user_status` , 
       `display_name` 
   FROM `wp_users` 
   ORDER BY `user_email` DESC;
   

   Analyze results

Comments are closed.