Update Or Insert A Row With MySQL And PHP

Many situations arise where you need to either insert or update some data in a table but which you will not be certain as to which function to perform. A common solution is to do a query on the table first to see if the data exists and then insert if it doesn't and update if it does. However, this creates an unnecessary overhead in that every time the code is run at least 2 queries are run.

A better way is to try to update the table and then use the mysql_info() function to detect how many rows where updated in the query and how many rows matched the parameters in the update query.

Take the following query.

UPDATE table SET value = "value" WHERE valueId = 2;

When run on a table the mysql_info() function returns the following result.

Rows matched: 0 Changed: 0 Warnings: 0

This shows us that the valueID of 2 doesn't exist in the table. We would therefore need to run an insert query.

The following bit of code will allow you to run an update query on a table, detect if the value exists and then insert data instead.

$sql = 'UPDATE table SET value = "value" WHERE valueId = 2';
mysql_query($sql);

// has the update worked?
preg_match('/Rows matched: ([0-9])/',mysql_info(),$match);
 if ($match[1] == 0) {
  // insert into database
  $sql = 'INSERT INTO table(valueID, value) VALUE(2, "value")';
  mysql_query($sql);
 }
}

Finally, you could argue that the use of the mysql_affected_rows() function might be a better solution. However, this only tells you the number of rows affected, so if an update query find the correct row but doesn't change the information then it will show a value of 0.