The website I'm working on now has a dashboard showing data entered during the previous week by various users. I select this data using a simple WHERE clause:
SELECT * FROM table WHERE WEEK(date, 1) = WEEK(CURDATE(), 1) - 1
However, the New Year is coming soon, and when a user tries to view the dashboard on, for example, 3rd or 4th of January, my code is clearly going to give me a wrong result, because the last week's number of 2010 is 52. So, what would be the best way to alter my query to take into account the change of the year. And also, it would be cool to make it possible to select data entered 2, 3, 4,... weeks ago.
解决方案
How about selecting the WEEK of the day seven days ago?
WHERE WEEK(date, 1) = WEEK(CURDATE() - INTERVAL 1 WEEK, 1)
This way you can select data entered 2,3,4 weeks ago:
WHERE WEEK(date, 1) = WEEK(CURDATE() - INTERVAL 2 WEEK, 1)