By midtoad on

the following code from user Taherk will display a list of the top posters on a site:

 $users = db_query("SELECT COUNT(nid) AS count, {users}.uid, {users}.name FROM {node} LEFT JOIN {users} ON {node}.uid = {users}.uid WHERE {node}.uid != 0 GROUP BY uid ORDER BY count DESC LIMIT 5");
while ($user = db_fetch_object($users)) {
print "<a href=\"blog/".$user->uid."\">".$user->name."</a><br>";
}

 $users1 = db_query("SELECT Count(uid) as count1 from users");
while ($user1 = db_fetch_object($users1)) {
print "<br>Total members: ".$user1->count1;
}

so far, so good. But I'd also like to display the actual no. of posts created by each user in the list. How would I do that?

e.g.
Fred 108
Amanda 101
Sigmund 78
Watson 56

thanks
S

Comments

nevets’s picture

nevets commented

I think you want to change

print "<a href=\"blog/".$user->uid."\">".$user->name."</a><br>";

to

print "<a href=\"blog/".$user->uid."\">".$user->name."</a> ($user->count)<br>";
midtoad’s picture

midtoad commented

That was quick! You must have posted the answer while I was working out the solution. Your answer and mine are the same, and both work, so thanks!

midtoad’s picture

midtoad commented

I just figured it out. The answer was in the preceding post! The number of posts was being determined in the original query and inserted in the temporary $users database object as 'count'. So it was just a question of displayed that field. Here's the modified code:

 $users = db_query("SELECT COUNT(nid) AS count, {users}.uid, {users}.name FROM {node} LEFT JOIN {users} ON {node}.uid = {users}.uid WHERE {node}.uid != 0 GROUP BY uid ORDER BY count DESC LIMIT 10");
while ($user = db_fetch_object($users)) {
print "<a href=\"blog/".$user->uid."\">".$user->name."</a>: ".$user->count." posts<br>";
}
midtoad’s picture

midtoad commented

the post count returned by this code differs wildly from the post count that you get by going to a user's profile and checking their Track page. that count seems correct, while this one does not.