Help With Toplists

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Help With Toplists

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT * FROM `users` ORDER BY `post_count` DESC LIMIT 10
simple enough..
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I don't have a post_count, I just number my post count in the forums by mysql_num_rows
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

then you need to perform a JOIN and use GROUP BY...
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Could you post an example, or point me towards some documentation?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

maybe after reading the following you'll understand the method to my madness .. ;)

http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
Post Reply