With PHP and MySQL I'm attempting to display products with many votes on the certain time period (round-the-clock, 7 days, 12 months, etc). When someone votes with an item, a table records the consumer, item id, election, and time, like so:
username | itemid | vote | time asdf | 127 | 1 | 1306726126 asdf | 124 | -1 | 1306726123 bob | 127 | 1 | 1306726129 bob | 124 | 1 | 1306726123
Now, I've another table with item particulars.
itemid | name | category | date | etc
What I wish to do is call a table to show all of the data from table 2 for just products with votes within the last 24hours and sort it through the votes. What this means is I have to SUM votes as time passes < 24 hrs, then RIGHT JOIN (?) to my other database? I'm not sure, I'm getting difficulty determining the way i is going about carrying this out. Any suggestiongs?
Something similar to this will work.
SELECT SUM(Table1.vote) as votes, Table2.* FROM Table2 LEFT JOIN Table1 ON Table1.itemid=Table2.itemid WHERE Table1.`time`>=DATE_SUB(Table1.`time`, INTERVAL 24 HOUR) GROUP BY Table1.itemid ORDER BY Table1.votes DESC