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