Total Users Per Role - SQL Help

Hi

With SQL what would be the query to count the total number of users per role.

So

Role Total
Administrator 1
Editor 5

and so on for each role?

Thanks

Wayne

  • Adam Czajczyk

    Hello Wayne,

    I hope you're well today and thank you for your question!

    A role assigned to the user is stored inside the "_usermeta" table as a value of "_capabilities" meta_key. It's a serialized array so there's not "straightforward" way to directly interfere with it but hopefully this will point you in a right direction:

    SELECT COUNT(umeta_id) FROM prefix_usermeta WHERE meta_key = 'prefix_capabilities' AND meta_value LIKE '%administrator%'
    
    This will return the number of users of "administrator" role. A technical note:
    - replace "prefix" with your current WP table prefix
    - remember to enclose table names inside backticks (I cannot post that on forum)
    
    That said, I'm not aware what would you like to use that SQL query for but wouldn't it be better to use WP core php functions for this? I'm just wondering though :)
    
    Best regards,
    Adam
  • Wayne

    Hi @Adam Czajczyk

    Thanks for the above the only issue with that is it does not show each role, it simply displays how many administrators

    If it can be done with PHP I would also use that to simply return a list of User Roles (This could use the user_role meta key from wp_usermeta so that it displays in a nice format) What I need is a two column table, column one User Role, column two total users ?

    I also have a custom meta field under usermeta of upme_user_profile_status which has status of ACTIVE or INACTIVE. Ideally the table could have column 3 and column 4 which are of the total for that role how many are ACTIVE (column 3) and how many are INACTIVE (column 4)

    Thanks

    Wayne

  • Adam Czajczyk

    Hello Wayne,

    Thanks for your response!

    Thanks for the above the only issue with that is it does not show each role, it simply displays how many administrators

    That's true. I forgot to add that you'd need to run this query multiple times (for each role), each time replacing "%administrator%" with proper role name (e.g. "%subscriber%"). I'm sorry for causing confusion. That said, interacting directly with WP database isn't a suggested solution unless really necessary.

    Thanks to the description you shared with me, I think I can be of a bit more help here, as this can be done with a PHP snippet. That said, I crafted this simple plugin for you. Take a look below please:

    <?php 
    
    /*
    Function returns list of existing WP user roles as an array:
    
    array ( [administrator] => Administrator.....)
    */
    
    function wpmu_get_user_roles() {
    	global $wp_roles;
        $roles = $wp_roles->get_names();
        return $roles;
    }
    
    /* Iterate through user roles and count users;
    returns another array:
    $rolesCount = (
    	[1] = (
    		role = 'administrator',
    		role_name = 'Administrator',
    		count = 2
    	),
    	[2] = .....
    } */
    function wpmu_count_role_users() {
    
    	$wpmu_user_roles = wpmu_get_user_roles();
    	$wpmu_user_roles_count = array();
    	$i=1;
    	foreach ($wpmu_user_roles as $key=>$value) {
    		$wpmu_user_roles_count[$i]['role'] = $key;
    		$wpmu_user_roles_count[$i]['role_name'] = $value;
    		$wpmu_user_roles_count[$i]['count'] = count(get_users(array('role'=>$key)));
    		$i++;
    	}
    	return $wpmu_user_roles_count;
    }
    
    /* show role users count simple table shortcode */
    function wpmu_count_role_users_shortcode() {
    
    	$role_table = wpmu_count_role_users();
    	$html = '<table class="wpmu_role_count">
    		<tr><td>User role</td><td>Number of users</td></tr>';
    	foreach ($role_table as $role_table_row) {
    		$html .= "<tr><td>".$role_table_row['role_name']."</td><td>".$role_table_row['count']."</td></tr>";
    	}
    	$html .= "</table>";
    
    	return $html;
    }
    add_shortcode('count-role-users','wpmu_count_role_users_shortcode');

    As you can see, there are free functions. Let me explain this briefly:

    wpmu_get_user_roles()

    Returns an array of existing WP user roles in form of an array where array key is an internal WP role identifier and array value is role name ;

    wpmu_count_role_users()

    Uses wpmu_get_user_role() to fetch list of available roles and then gets list of users for each role, counts them and adds to another array

    wpmu_count_role_users_shortcode()

    Wraps everything in a simple HTML table.

    How to use it?

    First, please create an empty text file with a .php extension, giving it any name you wish (e.g. count-role-users.php), paste the code inside this file and upload the file via FTP to your /wp-content/mu-plugins/ folder.

    If there's no /mu-plugins/ folder just create it.

    Second, place [count-role-users] shortcode anywhere on your post/page and you should end-up with a table enlisting roles along with number of users.

    You may want however to extend my code. That's why it's split into three functions so you could utilize returned tables wth PHP anyway you want.

    I hope that helps and if you'd need any further assistance on this, let me know please!

    Best regards,
    Adam

  • Adam Czajczyk

    Hello Wayne!

    I'm glad I could help :slight_smile:

    It's possible to strip some user roles, the only requirement here would be to adjust the code a bit. You will want to replace it with the following one:

    <?php 
    
    /*
    Function returns list of existing WP user roles as an array:
    
    array ( [administrator] => Administrator.....)
    */
    
    function wpmu_get_user_roles() {
    	global $wp_roles;
        $roles = $wp_roles->get_names();
        return $roles;
    }
    
    /* Iterate through user roles and count users;
    returns another array:
    $rolesCount = (
    	[1] = (
    		role = 'administrator',
    		role_name = 'Administrator',
    		count = 2
    	),
    	[2] = .....
    } */
    function wpmu_count_role_users($roles_ignore = array()) {
    
    	$wpmu_user_roles = wpmu_get_user_roles();
    	$wpmu_user_roles_count = array();
    	$i=1;
    	foreach ($wpmu_user_roles as $key=>$value) {
    		if (!in_array($key,$roles_ignore)) {
    			$wpmu_user_roles_count[$i]['role'] = $key;
    			$wpmu_user_roles_count[$i]['role_name'] = $value;
    			$wpmu_user_roles_count[$i]['count'] = count(get_users(array('role'=>$key)));
    			$i++;
    		}
    	}
    	return $wpmu_user_roles_count;
    }
    
    /* show role users count simple table shortcode */
    function wpmu_count_role_users_shortcode($atts) {
    
    	$roles_ignore = @explode(",","{$atts['ignore']}");
    	$role_table = wpmu_count_role_users($roles_ignore);
    	$html = '<table class="wpmu_role_count">
    		<tr><td>User role</td><td>Number of users</td></tr>';
    	foreach ($role_table as $role_table_row) {
    		$html .= "<tr><td>".$role_table_row['role_name']."</td><td>".$role_table_row['count']."</td></tr>";
    	}
    	$html .= "</table>";
    
    	return $html;
    }
    add_shortcode('count-role-users','wpmu_count_role_users_shortcode');

    This will work exactly the same as previously, except you can now add optional "ignore" attribute to the shortcode like this:

    [count-role-users ignore="administrator"]

    or

    [count-role-users ignore="administrator,editor"]

    As value for "ignore" attribute you will want to provide a list of WP user roles to be excluded.

    I hope that helps!

    Best regards,
    Adam

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.