MySQL Insert on Duplicate Key

insert, mysql, php

Solution

see INSERT ... ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

e.g.

INSERT INTO table (name,counter) VALUES ('Bob', 1)
  ON DUPLICATE KEY UPDATE counter=counter+1

Problem

I am building a rating system, and i want to insert a new row, if the `name` field does not already contain the `name` i want to insert, and if it does exist, i want to increase the `count` field by 1 For example, if i have a row the the `name` 'Tom' and i try to insert another row with the `name` 'Tom, then i want to `+1` for the field `count` on the row that already exists. If a row with the `name` 'Tom' does not exist, i want to insert a new one and set `count` to 1. I know i could do this with about 3 SQL statements and some `if` statements, but that would slow down the script as 2/3 sql commands are being executed. Any ideas? Thanks!

Original source