PHP INSERT ON DUPLICATE KEY IGNORE adding null values?

Question

i am using the following code to update a database from an xml file, everything works as intended but now i have to add 2x more fields to the table and update the data manually via a html form.

when i run the form i can insert data into the "afk" and the "remarks" fields, but upon running the following query it empties the fields.

$insert = "INSERT INTO `ecmt_memberlist` (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles, last_modified) VALUES('$characterID','$name','$startDateTime','$baseID','$base','$title','$logonDateTime','$logoffDateTime','$locationID','$location','$shipTypeID','$shipType','$roles','$grantableRoles','$modifiedTS') ON DUPLICATE KEY UPDATE 
name='$name',
startDateTime='$startDateTime',
baseID='$baseID',
base='$base',
title='$title',
logonDateTime='$logonDateTime',
logoffDateTime='$logoffDateTime',
locationID='$locationID',
location='$location',
shipTypeID='$shipTypeID',
shipType='$shipType',
roles='$roles',
grantableRoles='$grantableRoles',
last_modified = '$modifiedTS'";

is it possible to tell the query to ignore the values already stored in the extra 2x manual fields and keep the data intact?

many thanks

Check `insert ignore`: http://stackoverflow.com/questions/2366813/on-duplicate-key-ignore — complex857, Feb 19 '13 at 20:47

jeroen · Answer 1 · 2013-02-19T21:14:16.397

If you tell mysql to update a field with an empty value, it will, so as far as I can see you have three options (in order of my preference...):

Make sure that all the values from the form are loaded with the values from the database when you present it. If you don't want to show all values you can use hidden inputs;
Build the second section of your sql statement (after ON DUPLICATE KEY UPDATE) dynamically; only add the fields if the variable is not empty;
Adapt the second section of your sql statement (after ON DUPLICATE KEY UPDATE) to only update a specific number of fields. I would not recommend this really.

By the way, I would also recommend using a prepared statement with bound variables to avoid sql injection problems.

Edit: The second option would look something like:

$insert = "INSERT INTO `ecmt_memberlist` (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles, last_modified) VALUES('$characterID','$name','$startDateTime','$baseID','$base','$title','$logonDateTime','$logoffDateTime','$locationID','$location','$shipTypeID','$shipType','$roles','$grantableRoles','$modifiedTS') ON DUPLICATE KEY UPDATE
             name='$name'";
if (!empty($startDateTime))
{
  $insert .= ", startDateTime='$startDateTime'";
}
// etc.

Or if you have all your fields in an array, you could loop, add the strings to a new array and implode.

PHP INSERT ON DUPLICATE KEY IGNORE adding null values?

1 Answers1