Having created an insert into my DB for my feed I now need to be able to update it if a record exists. I have got this far with it but cannot get the update to work.
$xml= 'test-feed.xml'; // URL for feed.
try{
$feed = new SimpleXMLElement($xml, null, true);
}catch(Exception $e){
echo $e->getMessage();
exit;
}
foreach($feed->property as $property)
{
$id = mysql_real_escape_string($property->id);
$query = "SELECT prop_id FROM images WHERE prop_id = '$id'";
$result = mysql_query($query);
$rows = mysql_num_rows($result);
}
if($rows > 1)
{
$sqlxml = "";
$arr = array();
foreach($feed->property as $property) {
$propertyid = (string)$property->id;
foreach($property->images->image as $image) {
$i = 0;
$url = (string)$image->url;
$arr[] = "UPDATE images SET `url` = '$url' WHERE `prop_id` = '$propertyid' , ";
$i++;
}
}
foreach($arr as $result) $sql .= $result;
$sql = rtrim($sql, ",");
echo $sql;
}
else
{
echo " NO DATA SO INSERT";
}
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>';
}
The query structure echos as:
UPDATE images SET url = 'ImageId=X1000245' WHERE prop_id = 'A1234',
UPDATE images SET url = 'ImageId=X1000296' WHERE prop_id = 'A1234',
UPDATE images SET url = 'ImageId=X1000237' WHERE prop_id = 'ABC1234'
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 ' UPDATE
images SET `url` = '
Am I on the right track here? Can I update multiples using different WHERE clauses in the same query. I have been slugging away at this for a couple of days trying to figure it out for myself but once again I have to ask of you guys once more.
Blast where do I go from here. Prop_id is not unique only the URL which I am trying to update. I used UPDATE ON DUPLICATE KEY on another table and it works a charm.
– Table structure for table images
CREATE TABLE IF NOT EXISTS images ( id int(20) NOT NULL AUTO_INCREMENT, prop_id varchar(20) CHARACTER SET utf8 NOT NULL, url varchar(300) CHARACTER SET utf8 NOT NULL, primary enum(‘1’,‘0’) CHARACTER SET utf8 NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2227 ;
I am at a total loss as to how to progress with this. I need to parse the feed, check if prop_id exists, if it does update it else insert new row(s)
you cannot concatenate SQL statements with commas, you have to use semicolons
UPDATE images SET url = 'ImageId=X1000245' WHERE prop_id = 'A1234'[COLOR="Red"];[/COLOR]
UPDATE images SET url = 'ImageId=X1000296' WHERE prop_id = 'A1234'[COLOR="red"];[/COLOR]
UPDATE images SET url = 'ImageId=X1000237' WHERE prop_id = 'ABC1234'[COLOR="red"];[/COLOR]
UPDATE images SET url = 'ImageId=X1000245' WHERE prop_id = 'A1234';
UPDATE images SET url = 'ImageId=X1000296' WHERE prop_id = 'A1234';
UPDATE images SET url = 'ImageId=X1000237' WHERE prop_id = 'ABC1234';
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 'UPDATE images SET `url`=
!! CAUTION !!
Setting the interface up to allow multiple queries per statement makes the system more vulnerable to SQL injection attack. It’s ok to proceed with this approach, but keep that vulnerability in mind while doing so.
Thanks Anthony for the confirmation. I have no experience off MySQLi at all so another chance to learn something new. (Just what I need at the moment :)) Maybe I need to restructure my DB but not sure how I could make what I have any better, although I feel sure somebody would disagree with that statement.
prop_id is unique to the property but is inserted into the db multiple times, once for each image. prop_id ABC123 can have for example 10 image urls attached to it so the prop_id field will contain 10 ABC123 entries.
I did consider creating any extra field, strip the url down to say the last 5 digits of the url and insert that as a unique ID then use UPDATE ON DUPLICATE KEY. Workable solution perhaps?
Thanks Michael for your warning I will keep that in mind.
UPDATE images SET `url` = 'http://ImageId=X123' WHERE `prop_id` = 'A1234';
UPDATE images SET `url` = 'http://ImageId=X1044296' WHERE `prop_id` = 'A1234';
UPDATE images SET `url` = 'http://ImageId=X1000237' WHERE `prop_id` = 'A1234';
UPDATE images SET `url` = 'http://ImageId=X1000231' WHERE `prop_id` = 'ABC1234';
UPDATE images SET `url` = 'http://ImageId=X1000232' WHERE `prop_id` = 'ABC1234'
Successfully Inserted Records
The problem seems to be with the loop, all the url entries for each prop_id have the last value in the loop added to the db and not as echoed.
Thanks for the reply. In my frustration I decided to go in a different direction with this and put a unique field into the db. I am generating this by creating an extra field named pid. Then I am adding the image_id to the prop_id, which always creates a unique, insert this as the pid then I use ON DUPLICATE UPDATE.
Well it works!!
I did look at the PHP manual for multi queries with mysqli but to be honest I couldnt get my head around it
So I have ended up with the code below which may need a few tweaks to make it safe.
$xml = 'test-feed.xml'; // URL for feed.
try{
$feed = new SimpleXMLElement($xml, null, true);
}catch(Exception $e){
echo $e->getMessage();
exit;
}
$sql = 'INSERT INTO images (`prop_id`, `url`, `pid`) VALUES ';
$sqlxmlarr = array();
foreach($feed->property as $property) {
$propertyid = (string)$property->id;
$i = 0;
foreach ($property->images->image as $image) {
$url = (string)$image->url;
$imageid = (int)$image['id'];
$pid = "$propertyid-$imageid";
$sqlxmlarr[] = "('".$propertyid." ', '".$url."', '".$pid."'),";
$i++;
}
}
foreach($sqlxmlarr as $sqlchk) $sql .= $sqlchk;
$sql = rtrim($sql, ',') . ' ON DUPLICATE KEY UPDATE url = VALUES (url)';
echo $sql;
if(!mysql_query($sql)){
echo '<h1 style="color: red;">Error</h1><p>', mysql_error(), '</p>';
}
else
{
echo '<h1 style="color: red;">Property image records have been added to the database</h1>';
}
