Most active topics last 24 hours

[riverplate.com]

Hi!

i'm coding a cron that every hour gets the last 5 topics of the forum.

this it's the part where i've get the data

PHP Code:

$threadlist = $vbulletin->db->query_read("SELECT thread.threadid AS threadid, thread.title AS title 
                                          FROM " . TABLE_PREFIX . "thread AS thread
                                          WHERE thread.forumid = 9
                                          AND thread.open != 10
                                          AND thread.visible = 1
                                          ORDER BY thread.dateline DESC
                                          LIMIT 5"); 


but i'd like to show the most active (where users posted) topics within 24 hours.

is it possible? any ideas

thanks in advance

[riverplate.com]

any ideas?

[Opserty]

I think its

[minicode]ORDER BY thread.lastpost DESC[/minicode]

Instead of [minicode]ORDER BY thread.dateline DESC[/minicode]

[riverplate.com]

but i'd like to show the the "most posted" topics in the last 24 hours.

my query show the last 5 topics only

[Opserty]

Hmm that is more complicated, I don't know if you can use a COUNT in the WHERE clause of MySQL?

You'd have fetch the threads active in the last 24 hours, count the number of posts since then and order it by that...

[sql]
SELECT thread . * , post . *
FROM thread
LEFT JOIN post ON (thread.threadid = post.threadid )
WHERE post.dateline > ( UNIX_TIMESTAMP( ) -86400 )
GROUP BY thread.threadid
ORDER BY COUNT(post.postid) DESC
LIMIT 5
[/sql]

Not really a MySQL expert but that should do the trick I think. You will probably need to optimise/tweak it a bit.