Need help using ON DUPLICATE KEY UPDATE with PHP

ColinHughes · March 2, 2011, 2:46pm

My insert statement works just fine.

I now need to update the DB if id (unique key) exists. I am trying to do this using ON DUPLICATE KEY UPDATE but cannot seem to get it right.

I have:

$xml = 'test-feed.xml'; // URL for feed.



try{
  $feed = new SimpleXMLElement($xml, null, true);
}catch(Exception $e){
  echo $e-&gt;getMessage();
  exit;
}



$sql = "INSERT INTO sample (`id`, `status_date`, `status`) VALUES ";



foreach($feed-&gt;property as $property)
{

  $sql .= sprintf(
    "\
('%s','%s','%s'),",
      mysql_real_escape_string($property-&gt;id),
      mysql_real_escape_string($property-&gt;status_date),
      mysql_real_escape_string($property-&gt;status)
     
  );
}



$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE (`status_date` = '.$property-&gt;status_date.', `status` = '.$property-&gt;status.') ;';





echo $sql;

if(!mysql_query($sql)){
  echo '&lt;h1 style="color: red;"&gt;Error&lt;/h1&gt;&lt;p&gt;', mysql_error(), '&lt;/p&gt;';
}
else
{

  echo '&lt;h1 style="color: red;"&gt;Property data successfully added to database!&lt;/h1&gt;';
}

This produces an SQL syntax error

echoing my sql returns:

INSERT INTO sample 
(`id`, `status_date`, `status`) 
VALUES  
('A1234','2011-02-16 11:32:38','For sale'), 
('BB3102','2011-02-10 15:13:19','Pending'), 
('Abc186','2011-02-11 15:06:30','Sold'), 
('ABC1234','2011-02-11 15:06:30','For Sale'), 
('XYZ123','2011-02-10 15:17:12','Sold'), 
('WXY2468','2011-02-16 15:39:15','Pending') 
ON DUPLICATE KEY UPDATE 
(`status_date` = 2011-02-16 15:39:15, `status` = Pending) ;

Advice on the errors of my ways please.

Thanks in advance

Colin

tangoforce · March 2, 2011, 3:44pm

‘Pending’ by any chance?

r937 · March 2, 2011, 4:07pm

also, instead of 2011-02-16 15:39:15
you should use ‘2011-02-16 15:39:15’

r937 · March 2, 2011, 4:12pm

wait a sec…

which key are you duplicating here? id alone?

okay, in that INSERT statement, you are inserting a number of rows

and if any of the ids (‘A1234’,‘BB3102’,‘Abc186’,‘ABC1234’,‘XYZ123’,‘WXY2468’) is duplicated, then for that id, you want to set the datetime to one specific value with “pending” as the status

i notice that the datetime you’re defaulting to is the same as the last row’s…

weird

ColinHughes · March 2, 2011, 6:58pm

The id key is unique to each record and is set as unique in the db.

The id never changes its value but all other values may change. These values are taken from the xml feed and the datetime could be the same on one or more records.

For example:
(‘Abc186’,‘2011-02-11 15:06:30’,‘Sold’),
(‘ABC1234’,‘2011-02-11 15:06:30’,‘For Sale’)

Then the next time the xml feed is loaded it could return:
(‘Abc186’,‘2011-02-11 15:06:30’,‘Sold’),
(‘ABC1234’,‘2011-02-12 15:12:36’,‘Sold’)

If the id exists then update, if it doesn’t then insert.

I hope this explains what I am trying to achieve.

Thanks for your replies

Colin

r937 · March 2, 2011, 9:06pm

it doesn’t really explain what i was questioning

if you are trying to insert[indent]‘Abc186’ , ‘2011-02-11 15:06:30’ , ‘Sold’[/indent]then i don’t understand why, if Abc186 already exists, you feel it necessary to change that row to [indent]‘Abc186’ , ‘2011-02-16 15:39:15’ , ‘Pending’[/indent]

ColinHughes · March 2, 2011, 9:20pm

Sorry to confuse you.

Ok so Abc186 is a unique reference for a property. status_date is the date that any data related to this property was last changed. Status tells us whether the property is sold or whatever.

Every time the script is run it will parse the xml feed and either update the current info regarding property already in the db or, if no record is found, insert a new set of values into the db.

I hope this is a bit clearer.

There are actually more than 20 other fields attached to the id but I have left these out to simplify things during testing.

Colin

r937 · March 2, 2011, 9:34pm

no, it isn’t, and i don’t think you are seeing the issue i’m trying to raise

i understand what you’re trying to do (insert, and if it exists, update the status)

what i don’t understand is why you’re trying to update the status for ~any~ row that already exists to an arbitrary datetime with ‘Pending’ status

would it not make a lot more sense to use the VALUES option of the ON DUPLICATE KEY clause?

[ i’ll wait while you go and look that up ]

ColinHughes · March 2, 2011, 9:53pm

I think what you are trying to say is that I should only be updating the fields that have changed! Am I correct?

Colin

r937 · March 2, 2011, 10:03pm

you make it sound as if it is i who is having trouble (making my point) rather than you who is having trouble (understanding my point)

did you look up the VALUES part of the syntax yet?

ColinHughes · March 3, 2011, 8:47am

Sorry its pretty late here. I will take another look at the VALUES syntax tomorrow.

Thanks

Colin

ColinHughes · March 3, 2011, 11:17am

After some sleep and more thought I THINK I see your point.

This part is outside of the loop so will only ever insert the first result:

ON DUPLICATE KEY UPDATE status_date = VALUES 
("'.$property->status_date.'") +VALUES
 ("'.$property->status.'")';

which is why you say what “I don’t understand is why you’re trying to update the status for ~any~ row that already exists to an arbitrary datetime with ‘Pending’ status”

I have looked at the VALUES syntax and my interpretation of it is below:

$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE status_date = VALUES 
("'.$property->status_date.'") +VALUES 
("'.$property->status.'")';

Is this somewhere close to correct?

If I have got this so totally wrong I apologise.

Thanks for your time on this.

Colin

r937 · March 3, 2011, 11:42am

let’s go back to your original statement and illustrate the difference

you had this –

INSERT INTO sample 
(`id`, `status_date`, `status`) 
VALUES  
('A1234','2011-02-16 11:32:38','For sale'), 
('BB3102','2011-02-10 15:13:19','Pending'), 
('Abc186','2011-02-11 15:06:30','Sold'), 
('ABC1234','2011-02-11 15:06:30','For Sale'), 
('XYZ123','2011-02-10 15:17:12','Sold'), 
('WXY2468','2011-02-16 15:39:15','Pending') 
ON DUPLICATE KEY UPDATE 
(`status_date` = 2011-02-16 15:39:15, `status` = Pending) ;

what this said was…

if ‘A1234’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’
if ‘BB3102’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’
if ‘Abc186’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’
if ‘ABC1234’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’
if ‘XYZ123’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’
if ‘WXY2468’ already exists, set status_date = ‘2011-02-16 15:39:15’, status = ‘Pending’

in other words, no matter which one already exists, you clobber the data with the same values

now let’s have a look at this –

INSERT INTO sample
 (id, status_date, status)
VALUES
 ('A1234','2011-02-16 11:32:38','For sale')
,('BB3102','2011-02-10 15:13:19','Pending')
,('Abc186','2011-02-11 15:06:30','Sold')
,('ABC1234','2011-02-11 15:06:30','For Sale')
,('XYZ123','2011-02-10 15:17:12','Sold')
,('WXY2468','2011-02-16 15:39:15','Pending')
ON DUPLICATE KEY 
UPDATE status_date = VALUES(status_date)
     , status = VALUES(status)

what this says is…

if ‘A1234’ already exists, set status_date = ‘2011-02-16 11:32:38’, status=‘For sale’
if ‘BB3102’ already exists, set status_date = ‘2011-02-10 15:13:19’, status=‘Pending’
if ‘Abc186’ already exists, set status_date = ‘2011-02-11 15:06:30’, status=‘Sold’
if ‘ABC1234’ already exists, set status_date = ‘2011-02-11 15:06:30’, status=‘For Sale’
if ‘XYZ123’ already exists, set status_date = ‘2011-02-10 15:17:12’, status=‘Sold’
if ‘WXY2468’ already exists, set status_date = ‘2011-02-16 15:39:15’, status=‘Pending’

in other words, no matter which one already exists, you update it with the same values for that row that would’ve been inserted if it hadn’t already existed

see the difference?

ColinHughes · March 3, 2011, 1:07pm

OK thanks for the excellent explanation.

So now I have this:

$sql = "INSERT INTO sample (`id`, `status_date`, `status`) VALUES ";



foreach($feed-&gt;property as $property)
{

  $sql .= sprintf(
    "\
('%s','%s','%s'),",
      mysql_real_escape_string($property-&gt;id),
      mysql_real_escape_string($property-&gt;status_date),
      mysql_real_escape_string($property-&gt;status)
      
  );
}


$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE status_date = VALUES
 ("'.$property-&gt;status_date.'"),VALUES ("'.$property-&gt;status.'")';

echoing my SQL now returns:

INSERT INTO sample (`id`, `status_date`, `status`) VALUES
  ('A1234','2011-02-16 11:32:38','For sale'), 
('BB3102','2011-02-10 15:13:19','Sold'), 
('Abc186','2011-02-11 15:06:30','Sold'), 
('ABC1234','2011-02-11 15:06:30','For Sale'), 
('XYZ123','2011-02-10 15:17:12','Sold'), 
('WXY2468','2011-02-16 15:39:15','For sale') 
ON DUPLICATE KEY UPDATE  status_date = VALUES 
("2011-02-16 15:39:15"),VALUES ("For sale")

You have an error in your SQL syntax; check the manual that corresponds  to your 
MySQL server version for the right syntax to use near
  '"2011-02-16 15:39:15"),VALUES ("For sale")' at line 7

So now it is not returning all the results because it is outside of the foreach loop, correct?

Colin

r937 · March 3, 2011, 1:25pm

change this –

$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE status_date = VALUES
 ("'.$property->status_date.'"),VALUES ("'.$property->status.'")';

to this –

$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY 
UPDATE status_date = VALUES(status_date)
   ,status = VALUES (status);

ColinHughes · March 3, 2011, 2:01pm

Slight tweak

$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY  
UPDATE status_date = VALUES(status_date) 
   ,status = VALUES (status)';

and all is working well so far. Thanks so much for the explanation and example. Much clearer than the manual and so much better than if else statements.

Thanks again

Colin