Posted by Michelle on February 2, 2009 at 4:28am
Jump to:
| Project: | User Stats |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
I just wrote this for my site and thought I'd share it in case it's of use to anyone.
<?php
$sql = "SELECT u.uid,
u.name,
p.value * 1 as posts
FROM {profile_values} p LEFT JOIN {users} u on p.uid = u.uid
WHERE p.fid = 2 and u.uid not in (0,1,7,155)
ORDER by posts DESC";
$result = db_query_range($sql, 0, 20);
while ($poster = db_fetch_object($result) ) {
$top_posters[] = l($poster->name, "user/$poster->uid") . " ($poster->posts)";
}
$output = theme('item_list', $top_posters);
print $output;
?>Where it has "not in (0,1,7,155)" up there are users that I want excluded. You'd need to change those or delete that bit for your own site.
Michelle
Comments
#1
Wrong word in title. Though I suppose it could be the top 20 users, depending on your opinion. ;)
Michelle
#2
Hi Michelle. Thanks for this.
I get a couple of strangenesses with it. I get -
I should see more users than only 3. I am hoping to see the number of their posts, rather than "0".
Am I doing something wrong?
I did take out "and u.uid not in (0,1,7,155)" as that bit doesn't apply.
Many thanks
Nigel
#3
Oh, you also need to change p.fid = 2 to the correct id for "post count" in your profile_fields table. And, of course, this is only useful on the version that uses the profile module for storage.
Michelle
#4
As Michelle points out, if you're using the Drupal 6.x version of User Stats, there's a View that comes with the module which does this. So if possible, I'd recommend upgrading to 6.x. :)
#5
Michelle said, "change p.fid = 2 to the correct id for "post count" in your profile_fields table".
I don't have a 'post count' field in my profile_fields table. All the fields in that table are ones that have been custom designed by me. Should I have added such a field? I'm not getting this.
I'm in D5.15. Profile module is also v5.15.
Can't aspire to v6 yet.
Thanks
Nigel
#6
@nig: The post count field is added by the D5 version of User Stats which uses the profile module for storage. If you don't have that field, then either something is wrong with your installation of User Stats or possibly it was named differently in some older version. Liam would have to answer that one.
Michelle
#7
It's always been called the same thing. It should be in that profile_fields table.
The only way it could be something different is if you changed the field on the User Stats admin page -- if you don't know what I'm talking about, then it's doubtful you changed it. :)