CODEWITHSUNDEEP
phpmysqlranking
Evan Darwin
Evan Darwin

Reputation: 860

Simple rating system in MySQL

Let's say I am running a small vote counter product. The values are stored in the database "content", with a row containing the amount of votes ("count"), I also have a row called "rank", how would I autosum these values so that I don't have to iterate through every single one in PHP, causing the script to slow down tremendously?

Upvotes: 0

Views: 859

Answers (2)

Abraham Covelo
Abraham Covelo

Reputation: 961

If you have a structure like this in a table

item with fields: name, id, count, ...

you can simply do:

Up vote in the item with id = $id

UPDATE item SET count = count + 1 WHERE id='$id';

Down vote in the item with id = $id

UPDATE item SET count = count - 1 WHERE id='$id';

count will store the total number of votes.

If you want to check for avoiding more than one vote per user/ip you should store each individual vote in another table and check this aditional table for non repeated votes before sending the previous queries.

Upvotes: 2

TimWolla
TimWolla

Reputation: 32701

Something like this (depending on your exact table structure) should work:

UPDATE item SET rating = 
   (SELECT count(*) FROM rates WHERE type 'positive') - 
   (SELECT count(*) FROM rates WHERE type 'negative');

For more information have a look at the MySQL docs.

Upvotes: 0

Related Questions