[RESOLVED] Movie trailers - Top 5?

intenz

Hello. I've got this MySQL table with some movie trailers. In that table I've got the field HITS. How can I display current weeks top 5 movie trailers?

When someone views a trailer, I just update the HITS field by +1 WHERE id = trailer_id

I am so empty right now. :bemused: Can you please help me? 😕

Installer

Something like this:

SELECT id FROM my_table SORT BY hits DESC LIMIT 5

intenz

No, current weeks top 5. Not overall top 5.

notaloser

make a field weekhits, and do the same thing, +1 WHERE id = trailer_id for both overall hits and weekhits..

then clear your weekhits at the start of each week

SELECT * FROM my_table ORDER BY weekhits DESC LIMIT 5

intenz

notaloser, yea... very good solution and I figured something like that my self, but how can I clear the weekhits every week? I can't use crontab, so what do you suggest? 🙂

notaloser

cron jobs would be the best way, but if you can't do that, just manually run your script each week..

make a file called clearhits.php and run it every monday or something

$clear = mysql_query("UPDATE table SET weekhits = '0' ");

intenz

I can't use crontab. 😉 That's the problem.

notaloser

haha yeah i read that wrong, then edited the post ... hopefully before you checked on it but oh well

notaloser

infact, if thats too much work you could even write a script that has an if statement checking if the current date and time is 7 days past a variable $startingdate... and if it is, set the weekhits = 0 and add 7 more days to the $startingdate variable..

sort of like a cronjob.. just script it on a commonly visited page so that it will be run often enough

intenz

Yep, I did that and it works. Though it doesn't reset always on the same time, it will reset at least weekly, which should work.

$time_file = 'timer.file';
$ttime = file_get_contents( $time_file );
if( $ttime < time() )
{
	mysql_query( "UPDATE topp5_trailer SET week_sott = 0" );
	$fp = fopen( $time_file, 'w+' );
	fwrite( $fp, time() + 604800 );
	fclose( $fp );
}