Error message

The spam filter installed on this site is currently unavailable. Per site policy, we are unable to accept new submissions until that problem is resolved. Please try resubmitting the form in a couple of minutes.

Drupal - Get User Counts For Each User Role

Category: 

Here is how to get a count of users for each user role in a Drupal 6 or 7 site:

SELECT
  COUNT(u.uid) AS user_count,
  r.name AS role 
FROM {users} u 
  INNER JOIN {users_roles} ur ON u.uid = ur.uid
  INNER JOIN {role} r ON ur.rid = r.rid
GROUP BY r.name
ORDER BY user_count DESC;

(Note, this isn't the 'correct' way to do it in Drupal 7, we should really use the new Database API layer, but I'm lazy right now.)

This will give you a table something like this:

user_countrole
3administrator
7editor
23author

Here is a copy of the SQL query without Drupal's curly brackets:

SELECT
  COUNT(u.uid) AS user_count,
  r.name AS role 
FROM users u 
  INNER JOIN users_roles ur ON u.uid = ur.uid
  INNER JOIN role r ON ur.rid = r.rid
GROUP BY r.name
ORDER BY user_count DESC;

Comments

Theoretically, you could put it anywhere where draupl runs php (even evaluated blocks with php code), but ideally, you should put this code into your module. When you start a new draupl website, you should always start at least one module to put all your custom functionality into it. For example, you might need this to run on hook_nodeapi (to respond to some draupl's event), or on hook_menu callback, or whatever you please.

tyler's picture

Agreed, starting at least one custom module for each site is good practice, and code like this is best placed in there (or in a Drush script if we are feeling fancy).

It is quite hard to understand for begginers..u just use elaborate

tyler's picture

Hi Shavin,

These are MySQL queries that can be executed inside a custom Drupal module. If you're new to using MySQL in Drupal, check out this page:

https://drupal.org/developing/api/database

Drupal way Query is below :)

    $query = db_select('role', 'r');
    $query->leftJoin('users_roles', 'ur', 'ur.rid = r.rid');
    $query->addExpression('COUNT(ur.rid)', 'user_count');
    $query->fields('r', array('rid', 'name'));
    $query->groupBy('r.rid');

    $result = $query->execute()->fetchAllAssoc('rid');

Add new comment