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->getMessage();
exit;
}
$sql = "INSERT INTO sample (`id`, `status_date`, `status`) VALUES ";
foreach($feed->property as $property)
{
$sql .= sprintf(
"\
('%s','%s','%s'),",
mysql_real_escape_string($property->id),
mysql_real_escape_string($property->status_date),
mysql_real_escape_string($property->status)
);
}
$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE (`status_date` = '.$property->status_date.', `status` = '.$property->status.') ;';
echo $sql;
if(!mysql_query($sql)){
echo '<h1 style="color: red;">Error</h1><p>', mysql_error(), '</p>';
}
else
{
echo '<h1 style="color: red;">Property data successfully added to database!</h1>';
}
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