Help With Toplists
Moderator: General Moderators
Help With Toplists
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SELECT * FROM `users` ORDER BY `post_count` DESC LIMIT 10- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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 DESCWow 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?
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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
only if you store the author information as the username...scrotaye wrote:author = the username in the table so I would put that in place of username
correctWhere's post_count coming from (guessing it's what you're naming the count(author)
p.userid would normally be the user's id number that created the post..where's p_userid coming from? the unique id of each entry?
us is a table alias to the users table.Where's 'us' coming from?
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
Everything except count(author) is a shot in the dark for me, especcially p.userid
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- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
maybe after reading the following you'll understand the method to my madness .. 
http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf