Most active topics last 24 hours
 

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

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

is it possible? any ideas

thanks in advance

any ideas?

I think its

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

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

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

my query show the last 5 topics only

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.