StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Quick correction to some of the answers above. I believe OP is looking for AVG() instead of SUM() since there could be 1000 votes with 1 value and 100 with 5, so to get top rated, you'd need AVG. To get "rated most often", you'd do COUNT(). SUM() by itself doesn't actually tell you much. 200x5=1000x1=300x3+100x1</p> <p>If you change st_date to timestamp, you can also use timestampdiff in addition to other date functions mentioned in other solutions. Change DAY to MONTH or YEAR and the <7 to <1 for month/year or just leave as days and do 30/365</p> <pre><code>$query = "SELECT stories.*, AVG(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE abs(timestampdiff(DAY, stories.st_date, NOW())) < 7 GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10" </code></pre> <p>Also, the above query gives you the highest rated articles that were created within your desired period. If you decide to to see highest rated articles within a period NO MATTER when they were created, change your where clause to match the vote date</p> <pre><code>$query = "SELECT stories.*, AVG(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE abs(timestampdiff(DAY, votes.date, NOW())) < 7 GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10" </code></pre> <p>You can combine both article creation and the voting period in one query also.</p> <p>Last but not least, you should change name of 'date' column to something else like vote_date. Date is a reserved word and it's not a good idea to use that for column name</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload