"Most Active Users" and "Most Active Users This Week" block
Last modified: February 1, 2009 - 16:16
This code snippet displays two lists of users. The first list shows the most active users on the site since it started, and the second shows the most active users in the past week. The usernames are linked to their profiles if the user viewing the block has permission to visit user profiles, and the number of posts (comments and nodes) that a user has made are displayed next to the user's name. For example, the output could look like this:
- Dries (1000000000)
- IceCreamYou (1000)
Most Active Users Ever
- Dries (100)
- IceCreamYou (10)
Most Active Users in the Last Week
Note that this snippet will output both lists in the same block. If you wish, you can remove the title lines (the ones with echo "<h3>...</h3>"; in them) and put the code from each section into separate blocks.
<?php
echo "<h3>Most Active Users Ever</h3>";
$active_forever_result = db_query_range("
SELECT (COUNT(DISTINCT(n.nid)) + COUNT(DISTINCT(c.cid))) count, u.uid
FROM {users} u
LEFT JOIN {node} n ON u.uid = n.uid
LEFT JOIN {comments} c ON c.uid = u.uid
WHERE u.uid <> 0
AND (n.uid = u.uid OR c.uid = u.uid)
AND u.status = 1
GROUP BY n.uid
ORDER BY count DESC",
0, 5); //Change the second number in this line to the number of results you want to get.
$list = array();
while ($row = db_fetch_array($active_forever_result)) {
$account = user_load(array('uid' => $row['uid']));
$list[] = theme('username', $account) ." (". $row['count'] .")";
}
print theme('item_list', $list);
echo "<h3>Most Active Users in the Last Week</h3>";
$active_week_result = db_query_range("
SELECT uid, SUM(count) sum FROM (
(
SELECT u.uid, COUNT(DISTINCT(n.nid)) count
FROM {users} u
LEFT JOIN {node} n ON u.uid = n.uid
WHERE u.uid <> 0
AND n.created > %d - (7 * 24 * 60 * 60)
GROUP BY u.uid
) UNION (
SELECT u.uid, COUNT(DISTINCT(c.cid)) count
FROM {users} u
LEFT JOIN {comments} c ON c.uid = u.uid
WHERE u.uid <> 0
AND c.timestamp > %d - (7 * 24 * 60 * 60)
GROUP BY u.uid
)
ORDER BY count DESC
) as x
GROUP BY uid
ORDER BY count DESC", time(), time(),
0, 5); //Change the second number in this line to the number of results you want to get.
$list = array();
while($row = db_fetch_array($active_week_result)){
$account = user_load(array('uid' => $row['uid']));
$list[] = theme('username', $account) ." (". $row['sum'] .")";
}
print theme('item_list', $list);
?>
Very helpful!
Many thanks! One question though: how would I go about getting it to count a particular content type only... like forum posts and comments only?
(Or if that's too complex, just exclude a couple of user id's...user 1 I think I can figure out)
Just change the WHERE clauses
For node type you would add something like
AND n.type = 'page'in the WHERE clause; to exclude UIDs you would add something likeAND u.uid <> 1. You may also want to addAND n.status = 1(if I remember the column name correctly) to make sure only published nodes appear.Thanks
Thanks.
By the same logic, I could add WHERE conditions to narrow it to specific role ?
and if I want to go 30 days instead of last week...
AND n.created > %d - (7 * 24 * 60 * 60)has to change, am I right?I've played w/it some but have no idea what the parameters mean and I don't seem to be getting a 30 day count (changed the 7 to 30 and left the rest)
More or less
To narrow to a specific role requires a join on the {users_roles} table and manipulation of the $user->roles array. It's a little complicated if you don't know what you're doing.
You did the right thing to get 30 days of activity. You probably just need to refresh your cache or something.
Your best chance for more help on this is the MySQL documentation, PHP documentation, Google, and the Drupal forums, in that order.
Something broken?
There's a bug here somewhere.... or I've broken it with my edits.
What's happening is that I'm seeing different users show up under "most active ever" with a post count of 157. It's not always the same person... but it's always somebody with far fewer than 157 posts.
Any ideas? This is the current state of the snippet I'm using. Maybe I introduced an error somewhere...
<?phpecho "<h3>Most active ever</h3>";
$active_forever_result = db_query_range("
SELECT (COUNT(DISTINCT(n.nid)) + COUNT(DISTINCT(c.cid))) count, u.uid
FROM {users} u
LEFT JOIN {node} n ON u.uid = n.uid
LEFT JOIN {comments} c ON c.uid = u.uid
WHERE u.uid <> 0
AND (n.uid = u.uid OR c.uid = u.uid)
AND u.status = 1
GROUP BY n.uid
ORDER BY count DESC",
0, 5); //Change the second number in this line to the number of results you want to get.
$list = array();
while ($row = db_fetch_array($active_forever_result)) {
$account = user_load(array('uid' => $row['uid']));
$list[] = theme('username', $account) ." (". $row['count'] .")";
}
print theme('item_list', $list);
?>
thank you for this very useful snippet
thank you for this very useful snippet. It was exactly what I was looking for, an easy way to list the active bloggers in the last month.
Although I modified it to list the bloggers I still have a couple of questions for modifying this code:
1. since i use this code to list the most active bloggers how could I make the link to point not to the user account but to the corresponding blog adress?
2. is there an easy way to modify this code to list not the most active users but the most active taxonomy terms from a certain vocabulary?
Thank you again
reality is for people who lack imagination
How about views integration
Is there a way to use these code as a views 2 filter. Then I can build my own user-block with views 2. I "only" need to filter the most active users.
Thanks
this is great and works for
this is great and works for just about exactly what we need, but how do i pull and display the photo that is associated with a user instead of their username?