Organize by weeks
Posted: Sat Dec 19, 2009 12:11 pm
Hello. I have a system where you can vote for some songs. The votes are counted weekly. The database has a table where i record the vote id, the user id, the song id and the timestamp. I want to be able to show the count of the votes weekly but also let the user to choose which week they want to see the votes. I have a mysql query that gives me the count of the votes for this week, but dont know how to show it for each week. The query is :
Thanks
Code: Select all
SELECT p.member_id, COUNT(v.song) total
FROM votes v
JOIN playlist p
ON v.song = p.song_id
WHERE WEEK(`v.timestamp`,1) = WEEK(CURDATE(),1)
GROUP
BY v.song
ORDER BY total DESC
Thanks