I have 1 table filled with articles. For the purpose of this post, lets just say it has 4 fields. story_id, story_title, story_numyes, story_numno
Each article can be voted YES or NO. I store every rating in another table, which contains 3 fields: vote_storyid, vote_date (as a timestamp), vote_code (1 = yes, 0 = no).
So when somebody votes yes on an article, it run an update query to story_numyes+1 as well as an insert query to log the story id, date and vote_code in the 2nd table.
I would like to sort articles based on how many YES or NO votes it has. For "Best of all time" rating is obviously simple.... ORDER BY story_numyes DESC.
But how would I go about doing best/worst articles today, this week, this month?
I get the timestamps to mark the cut-off dates for each period via the following:
$last_week = strtotime("last week");
$last_month = strtotime("last month");
But Im not sure how to utilize these timestamps in a mysql query to achieve the desired results.
posted date: 2009-04-10 16:16:00