Counting users
drupalworld - April 19, 2007 - 15:21
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
Try this
<?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
script not working
this script is not working, any idea whats missing?
grandmaster
Hmm...
Did you close out the PHP code? This worked for me when I wrote it.
error comes up!
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:
<?php$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
Because it is
Changing
COUNT(uid)toCOUNT(u.uid)should solve the problem.Still having error
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
I think print
I think
print db_result(db_query($sql));should be
print db_result($result);That worked, thanks!!
That worked, thanks!!
If you want the count for all roles this may help
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 />';
}
?>
Thanks both of you for these
Thanks both of you for these snippets :)
Very useful indeed!
Drupalworld