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

$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

ORDER BY thread.lastpost DESC

Instead of ORDER BY thread.dateline DESC

[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...


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


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