By drupalworld on

I found this great snippet to count how many people have registered an account on my site:

<?php
$sql = 'SELECT COUNT(uid) as count FROM {users} where status = 1';
$count = db_result(db_query($sql));
print $count;
?>

But I would like to know if there is way to count how many registered users from a specific role?
Many thanks,
Drupalworld

⋅ Categories: Drupal 5.x

Comments

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented 
<?php
$rid = 3;   /* <-- select the correct role id */
$result = db_query("SELECT COUNT(uid) as count FROM {users} u
INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = %d
AND u.status = 1", $rid);
print db_result(db_query($sql));

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

grandmaster’s picture

grandmaster commented

this script is not working, any idea whats missing?

grandmaster

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Did you close out the PHP code? This worked for me when I wrote it.

NancyDru

grandmaster’s picture

grandmaster commented

yes, i closed out the php code, this is the error which shows up:

user warning: Column 'uid' in field list is ambiguous query: eval SELECT COUNT(uid) as count FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid WHERE ur.rid = 3 AND u.status = 1 in .../includes/database.mysql.inc on line 172.
user warning: Query was empty query: eval in .../includes/database.mysql.inc on line 172.

here's the code:

$rid = 3;
$result = db_query("SELECT COUNT(uid) as count FROM {users} u
INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = %d
AND u.status = 1", $rid);
print db_result(db_query($sql));

~ grandmaster

nevets’s picture

nevets commented

Changing COUNT(uid) to COUNT(u.uid) should solve the problem.

BluesmanEP’s picture

BluesmanEP commented

I changed COUNT(uid) to COUNT(u.uid) and I'm still getting error messages:

"user warning: Query was empty query: in /home/nydac02/public_html/includes/common.inc(1547) : eval()'d code on line 6."

I'm trying to display the number of users that belong to a specific role.

Thanks!

Evan

nevets’s picture

nevets commented

I think

print db_result(db_query($sql));

should be

print db_result($result);
BluesmanEP’s picture

BluesmanEP commented

That worked, thanks!!

nevets’s picture

nevets commented

This snippet will show the count of registered users (uid > 0) that are active (status = 1) per role (as long as there is at least one registered user for the role). Note that if you allow/have users with more than one role, the sum of the counts will be greater than the number of users.

<?php
$results = db_query('SELECT COUNT(u.uid) as count, r.name FROM {users} u
JOIN {users_roles} ur ON u.uid=ur.uid JOIN {role} r ON (ur.rid = r.rid) WHERE u.status = 1 AND u.uid > 0
GROUP BY ur.rid');
while ( $data = db_fetch_object($results) ) {
print $data->count . ' ' . $data->name . '<br />';
}
?>
drupalworld’s picture

drupalworld commented

Thanks both of you for these snippets :)
Very useful indeed!
Drupalworld

theorichel’s picture

theorichel commented

I should ask: how do I use this snippet? Can I - after making sure that the input format is right - just paste this in a block and put that on my user page? Of course I could experiment with it, but since this can have unforeseen consequences I'd rather inquire first.

Thx

TR

nevets’s picture

nevets commented

Correct, you can use the snippet in the way you described.

theorichel’s picture

theorichel commented

One tip for whoever tries this as well: If you use a prefix in your database, do not put this prefix before the table names in the above code. Drupal takes care of that.

Thanks

theorichel’s picture

theorichel commented

to show the number of blocked users as well?

Thanks

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Simply repeat the query with WHERE u.status = 0.

NancyDru

NancyDru

theorichel’s picture

theorichel commented

what i do not understand is that only the custom roles are reported, not the built-in role of the plain registered user. Can anything be done about that?
Thanks

question withdrawn, this thread starts with the solution Sorry