By using this site you agree to the use of cookies by Brugbart and our partners.

Learn more

ON DUPLICATE KEY

How to use ON DUPLICATE KEY with php and MySQL

Created: 2011-05-01 13:27

A common problem that beginners face, is how to replace a record, if it already exists in the database table.

Some will then execute a select query, to check if the record exists, which may in turn require additional validation on the scripting-side.

The ON DUPLICATE KEY option solves this problem, and will allow you to instruct MySQL to execute another query if a duplicate key is encountered.

ON DUPLICATE KEY Syntax

The below could be the beginning to a list of unique visitors, and would only insert a new record for first-timers. For everyone else, it would simply update the time stamp.

INSERT INTO ips (ip, tmstamp) VALUES ('$ip', '$time') ON DUPLICATE KEY UPDATE tmstamp = '$time'

The full PHP would be as follows.

mysql_query("INSERT INTO ips (ip, tmstamp) VALUES ('$ip', '$time') ON DUPLICATE KEY UPDATE tmstamp = '$time'", $Connection) or die(mysql_error());

Updating multiple values

If you got multiple updates to make, your syntax would be similar to that of a normal update.

INSERT INTO ips (ip, tmstamp) VALUES ('$ip', '$time') ON DUPLICATE KEY UPDATE tmstamp = '$time', whatever = 'test'