SQL Query request...

[Albus]

Could somebody write me the syntax for an SQL query that would basically return the top ten posters of the day? Resultset should include userid, username and postcount for the day. I have a similar query that does overall top ten, but I want a second one that will show the same thing, only for today (since midnight OR over the last twenty four hours). I will mention the name and website of the person who writes this query for me on my website. Thanks in advance.

[Tekton]

do you already have a field (or is there? not sure) for how many posts are made in that day?

[rake]

It'll be a little more complicated than showing the overall top ten.

Firs you need the time interval...
$searchdate = time() - 24 * 60 * 60;

then the query:
SELECT post.*, COUNT(userid) AS count FROM post WHERE dateline>$searchdate GROUP BY userid ORDER BY count DESC LIMIT 10

You'll get userame, id, and count will be the number of posts made in the last 24 hours. ;)

[Albus]

do you already have a field (or is there? not sure) for how many posts are made in that day?
There's a field in 'user' called 'posts' but that only has the total, which is where my 'Overall Top Posters' data comes from. This query may need to come from the posts table itself. I just don't know what to compare the 'dateline' field to in order to get everything since midnight.

[rake]

modify searchdate in my post above so that instead of 24*60*60 (24 hours) it's the time that has passed from midnight till now.

[Albus]

There's a field in 'user' called 'posts' but that only has the total, which is where my 'Overall Top Posters' data comes from. This query may need to come from the posts table itself. I just don't know what to compare the 'dateline' field to in order to get everything since midnight.
Much obliged Rake. Sorry I didn't refresh before posting my last message...


...it works now and can be seen at: http://www.wizardingrealm.com/index.php?styleid=8

[Albus]

Much obliged Rake. Sorry I didn't refresh before posting my last message...


...it works now and can be seen at: http://www.wizardingrealm.com/index.php?styleid=8
Your credit can be seen here: http://wizardingrealm.com/showpost.php?p=19797&postcount=18 Thanks!

[rake]

Aww, thanks. :)