1

I have voting system on my website, it stores each users vote in table called skmp_voting each record in this database has id, item_id (this is id of article user voted on), and vote_value . vote_value depend's on what users did, if they voted up value is "1" if they voted down value is "-1".

I have toparticles.php page where I wan't to display top articles, so articles that have more up votes. Here is my mysql query to get top articles I have now:

SELECT stories.*, skmp_votes.vote_value FROM stories 
JOIN skmp_votes ON stories.id = skmp_votes.item_id 
ORDER BY skmp_votes.vote_value DESC

It select's article information from other table called stories and put's it against vote_value from skmp_votes table.

I'm pretty confident that this isn't right, as it selects vote_value that is equal to 1 or something, so I need to somehow count all vote_values together and then use mysql query to get top articles.

Ilja
  • 44,142
  • 92
  • 275
  • 498
  • To avoid confusion, I think you should alter your scores to 1 and 0, not 1 and -1. A row in the table counts as a vote, but 1 equals up, and 0 equals down. The math gets funny when you add in negatives. – Bryan Sep 17 '11 at 17:08
  • in this case would following query "SELECT * FROM {$this->votes_table} WHERE `item_id`= $story_id AND `vote_value`<0"; – Ilja Sep 17 '11 at 17:28
  • look like this instead "SELECT * FROM {$this->votes_table} WHERE `item_id`= $story_id AND `vote_value`=0"; not sure where to add one = sign or two == – Ilja Sep 17 '11 at 17:29
  • Hi, the thing is that I can't change -1 to zero as I need it like that for another thing on my website. – Ilja Sep 18 '11 at 12:27

1 Answers1

1

It depends on your voting table. As I imagined, it has a row for each vote. In this case, you'll have to do a sum for the votes wich match an article. i.e.

SELECT SUM(vote_value) as 'total_votes' FROM skmp_voting WHERE item_id='$article_id';

You use SUM instead of COUNT becouse you want to substract the value from the negative votes.

EDIT: complementing the answer

This following Query will get you every article and its total votes, ordered by the total votes (the most voted articles at the top)

SELECT stories.*, SUM(skmp_votes.vote_value) as 'total_votes' FROM stories 
  JOIN skmp_votes ON stories.id = skmp_votes.item_id 
  ORDER BY skmp_votes.total_votes DESC

To get, say the 5 most voted articles, you just add at the end LIMIT 5

Daniel
  • 1,321
  • 12
  • 25
  • Gives an error, it work's fine with initial query, but when I added your one it did not display any articles and gave warring. ((( Please Tell me if you need any additional info on my database structure to help – Ilja Sep 17 '11 at 16:19
  • what's the warning? I just cought it. in ORDER BY skmp_votes.total_votes DESC total_votes is an artificial column, wich does not exist in skmp_votes. change that line for ORDER BY total_votes DESC – Daniel Sep 17 '11 at 23:29
  • Tried it, but doesn't seem to work either, it only shows one record that is not even top rated, I think the issue is with 1 and -1 values I use for votes, but I can't change -1 value to 0 or anything else, because I need it like that for another thing on the website, is there a way to modify this query to only sum UP votes? i.e. SUM skmp_votes.vote_value WHERE vote_value > 0 (I tried it like that, but id didn't work) – Ilja Sep 18 '11 at 12:27
  • edit your initial post and add the structure of the votes tables. – Daniel Sep 18 '11 at 14:26
  • try this: SELECT stories.*, SUM(skmp_votes.vote_value) as 'total_votes' FROM stories JOIN skmp_votes ON stories.id = skmp_votes.item_id GROUP BY stories.id ORDER BY skmp_votes.total_votes DESC – Daniel Sep 18 '11 at 14:27
  • yep this last one seem's to work perfectly ))) just needed to changeORDER BY skmp_votes.total_votes to ORDER BY total_votes Thank you very much ))) – Ilja Sep 19 '11 at 16:15