Have you ever wanted to write a single query that would update fields in a table – but you can’t be 100% sure the record exists yet for you to update? For example, you might have a table that holds configuration data for your application. There will be one record for each user in your system. You could use their “UserID” as the primary key (that is crucial to making this work).

Well, instead of doing this:

<?php
$sql = "SELECT COUNT(UserID) FROM configuration WHERE UserID='SomeUser'";
$result = mysqli_query($db,$sql);
if ($result && mysqli_num_rows($result)>0) {
$aResult = mysqli_fetch_array($result);
$iRecordExists = ($aResult[0]>0?1:0);
}

if ($iRecordExists>0) {
//do an update
$sql = "UPDATE configuration SET someField='someValue' WHERE UserID='SomeUser'";
mysqli_query($db,$sql);
}
else {
//do an insert
$sql = "INSERT INTO configuration SET someField='someValue', UserID='SomeUser'";
mysqli_query($db,$sql);
}
?>

You could just do this:

<?php
//insert the user's configuration field - if the record already exists - update instead
$sql = "INSERT INTO configuration SET UserID='SomeUser', someField='someValue' ON DUPLICATE KEY UPDATE someField='someValue' ";
mysqli_query($db,$sql);
?>

Simply put, the query will attempt to insert the configuration record first. If it finds that the specified UserID already has a configuration record in the table, it will simply update the existing record according to the values you include after “ON DUPLICATE KEY UPDATE”. You can include more than one field to update as well.

[Update: As Paul questioned in the comment below, the WHERE clause is not correct (in my original post). The trick is, you have to include the primary key as part of the insert statement - such as UserID in the example above.]

• Previous Entry: Sunrise Over Asia
• Next Entry: The Theory of Huckativity