mysql update if some values exists otherwise create a new entry

Question

I have a table rating with these fields rate_id, game_id, rating, ip. Let suppose that these fields has the following values 1,130,5,155.77.66.55

When a user try to vote for a game, I want with mysql to check if he has already vote for this game so mysql will check if ip and game_id already exists, if they exists then mysql will update the value of rating otherwise will create a new entry.

What is a efficient way to do this?

Answer 1

Create unique index that covers ip + game_id. After that you can use INSERT ... ON DUPLICATE KEY UPDATE statement.

So the total query will be something like

INSERT INTO rating (rate_id, game_id, rating, ip) VALUES (1,130,5,'155.77.66.55')
ON DUPLICATE KEY UPDATE rating = 5

Answer 2

MySQL allows an on duplicate key update syntax for INSERT. So if you set your key to be game_id, user_id (or whichever way you identify the user) then you can use INSERT...on DUPLICATE KEY UPDATE which will do just that:

http://dev.mysql.com/doc/refman/5.5/en/insert.html

Answer 3

You could also take a look at REPLACE INTO. Maybe not for this project but for future reference:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

from: dev.mysql.com

Answer 4

// check to see if exist
$sql = "SELECT ip FROM rating WHERE ip=$ip && game_id={$game_id}";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

if(isset($row['ip'])){
  mysql_query("UPDATE HERE");
}else{
  mysql_query("INSERT HERE");
}