Error message

Deprecated function: implode(): Passing glue string after array is deprecated. Swap the parameters in drupal_get_feeds() (line 394 of /home1/tylerfra/public_html/includes/common.inc).

Drupal - Get User Counts For Each User Role

tyler's picture
Submitted by tyler on Thu, 08/09/2012 - 16:38
Updated: 2013-04-02
Category: 
Code
Tags: 
drupal 6.x
drupal 7.x
planet drupal

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

Jon (not verified)

Fri, 10/12/2012 - 06:39

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

tyler

Fri, 10/12/2012 - 14:26

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).

shavin (not verified)

Thu, 02/20/2014 - 01:47

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

tyler's picture

tyler

Thu, 02/20/2014 - 09:09

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

angarsky (not verified)

Wed, 02/18/2015 - 08:05

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');