Mysql How To Insert New Record Or Update A Field Depending On Whether It Exists?

I am trying to implement a rating system where I keep the following two fields in my db table: rating (the current rating) num_rates (the number of ratings submitted so far) UPDATE

Solution 1:

Have a look at INSERT ... ON DUPLICATE KEY UPDATE.

It should look something like that:

INSERT INTO mytable (rating, num_rates, uniqueCol)
VALUES ($theRating, 1, $uniqueCol)
ON DUPLICATE KEY UPDATE
  rating=((rating*num_rates)+$theRating)/num_rates,
  num_rates=num_rates+1;

Make sure to have a UNIQUE index or PRIMARY KEY on your uniqueCol.