I have an online highscores made with php + mysql but it currently shows the All Time highscores, I want to add Daily/Weekly/Monthly to that and I was wondering what would be the best way todo that?
My current thought is to add 3 new tables and then have the data inserted into each of them, and then having a cron which would run at the appropriate times to delete the data from each of the tables.
Is there any better way I could do this?
Another thing, I want to have it so the page would be highscores.php?t=all t=daily, etc. How would I make it so that the page changed the query depending on that value?
Thanks.
Use one table and add a column with the date of the highscore. Then have three different queries for each timespan, e.g.
SELECT ... FROM highscores WHERE date>"05-12-2011";
If you want to have a generic version without the need to have a fixed date, use this one:
SELECT ...
FROM highscores
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY;