每日/每周/每月高分榜

Question

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.

Answer 1

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;