Page 1 of 1
Help With Toplists
Posted: Fri Feb 25, 2005 9:42 pm
by s.dot
So, for example I need a list of my top 10 forum posters. Showing their username, and number of posts. I need some strategic advice on how to do this.
Posted: Fri Feb 25, 2005 9:43 pm
by feyd
Code: Select all
SELECT * FROM `users` ORDER BY `post_count` DESC LIMIT 10
simple enough..
Posted: Fri Feb 25, 2005 9:50 pm
by s.dot
I don't have a post_count, I just number my post count in the forums by mysql_num_rows
Posted: Fri Feb 25, 2005 10:05 pm
by feyd
then you need to perform a JOIN and use GROUP BY...
Posted: Fri Feb 25, 2005 10:07 pm
by s.dot
Could you post an example, or point me towards some documentation?
Posted: Fri Feb 25, 2005 10:11 pm
by feyd
Code: Select all
SELECT
COUNT(username) post_count, us.*
FROM
users us
INNER JOIN
posts p
ON
p.userid = us.userid
GROUP BY
p.userid
ORDER BY
post_count DESC
Posted: Fri Feb 25, 2005 10:18 pm
by s.dot
Wow that looks confusing as hell
author = the username in the table so I would put that in place of username
Where's post_count coming from (guessing it's what you're naming the count(author)
where's p_userid coming from? the unique id of each entry?
Where's 'us' coming from?
Part of the SQL statement?
Posted: Fri Feb 25, 2005 10:26 pm
by feyd
scrotaye wrote:author = the username in the table so I would put that in place of username
only if you store the author information as the username...
Where's post_count coming from (guessing it's what you're naming the count(author)
correct
where's p_userid coming from? the unique id of each entry?
p.userid would normally be the user's id number that created the post..
Where's 'us' coming from?
us is a table alias to the users table.
Posted: Fri Feb 25, 2005 10:45 pm
by s.dot
I'm still not getting this
I have these tables in my forumentries table (which records all entries to the forum)
id = unique id for each entry
entry = the users post
time = time posted
topicid = the topicid that the post corresponds to
forumid = the forumid that the post corresponds to
istopic = if the post is a topic
time2 = time(); for forum listing
threadstatus = locked or open
So far, according to your post I have this
Code: Select all
SELECT
COUNT(author) post_count, us.*
FROM
users us
INNER JOIN
posts p
ON
p.userid = us.userid
GROUP BY
p.userid
ORDER BY
post_count DESC
Everything except count(author) is a shot in the dark for me, especcially p.userid
Posted: Fri Feb 25, 2005 11:06 pm
by feyd
maybe after reading the following you'll understand the method to my madness ..
http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf