I have an XML feed which updates/ inserts data into a MySQL database using a cron job. This works fine up to the point when an item of data in the feed is no longer updated but simply deleted. This data is not deleted from the DB and is still available which is what I am trying to prevent . I have the following code so far.
<?php
//////////////////// DELETE ITEMS REMOVED FROM FEED //////////////////////////
// LOOP THROUGH ITEM ID'S CURRENTLY IN FEED
foreach($xml->property as $property) :
$propertyid = $property->id;
$removed .= sprintf(
"\
%s,",
$propertyid
);
endforeach;
$removed = rtrim($removed, ',') ;
// SELECT ITEMS IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
$query = "SELECT * FROM `feed` WHERE `ref` NOT IN ('$removed')";
$result = mysql_query($query);
if (isset($result)):
while ($row = mysql_fetch_array($result)):
$ref1 = $row['ref'];
echo "<br />Affected Reference(s): " . $ref1 ;
endwhile;
endif;
////////////////////////////////////////////////////////////////////////
?>
The query prints like this: SELECT * FROM feed WHERE ref NOT IN (’ 12345, 33102, 33186, 33210, 37701’)
I am not sure about the use of NOT IN ??
What I need the query to do is select the ref that are not contained in the feed so that I can delete them.
I think it will work if you remove the quotes from (’ 12345, 33102, 33186, 33210, 37701’)
By putting quotes you tell the program that all these numbers are one string value.
If it is not a resource it is probably the boolean value false, which means that an SQL must have occurred.
You can check that by rewriting
$result = mysql_query($query);
as
$result = mysql_query($query) or die(mysql_error());
Remove the quotes as Dennis suggested but put the quotes around each id and see if that will work. So update this code $removed .= sprintf(
"
%s,",
$propertyid
<?php
//////////////////// DELETE ITEMS REMOVED FROM FEED //////////////////////////
// LOOP THROUGH ITEM ID'S CURRENTLY IN FEED
foreach($xml->property as $property) :
$propertyid = $property->id;
$removed .= "'$propertyid',";
endforeach;
$removed = rtrim($removed, ',') ;
// SELECT ITEMS IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
$query = "SELECT * FROM `feed` WHERE `ref` NOT IN ($removed)";
$result = mysql_query($query);
if (isset($result)):
while ($row = mysql_fetch_array($result)):
$ref1 = $row['ref'];
echo "<br />Affected Reference(s): " . $ref1 ;
endwhile;
endif;
////////////////////////////////////////////////////////////////////////
?>
I now need to delete all records from the DB that are not contained in the feed. So far I have this which does not seem to be working!!
//DELETE ENTRIES IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
// SELECT ITEMS IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
$query = "SELECT * FROM `feed` WHERE `ref` NOT IN ($removed)";
$result = mysql_query($query);
if (isset($result)):
while ($row = mysql_fetch_array($result)):
$ref1 = $row['ref'];
$query1 = "DELETE FROM `feed_property`, `feed_image`, `feed_char`\
"
. "USING `feed_property` INNER JOIN `feed_image` INNER JOIN `feed_char`\
"
. "WHERE feed_property.ref = '$ref1'\
"
. " AND feed_image.ref = '$ref1'\
"
. " AND feed_char.ref = '$ref1'; ";
if(!mysql_query($query1)):
echo '<h1 style="color: red;">Error</h1><p>', mysql_error(), '</p>';
else:
echo '<h1 style="color: red;">Properties have been removed from the database</h1>';
endif;
endwhile;
endif
echoing query1 gives:
DELETE FROM `feed_property`, `feed_image`, `feed_char` USING `feed_property` INNER JOIN `feed_image` INNER JOIN `feed_char` WHERE feed_property.ref = '12345' AND feed_image.ref = '12345' AND feed_char.ref = '12345';
Am I correct in thinking this is possibly not the best way to do this because I am looping through the query rather than the values
I’m not sure you need those newline (
) characters. Remove them and see what happen. My knowledge of MySQL is limited. Perhaps members know little more will chime in.
DELETE FROM feed_property, feed_image, feed_char USING feed_property INNER JOIN feed_images INNER JOIN feed_char WHERE feed_property.ref = 1261 AND feed_image.ref = 1261 AND feed_char.ref = 1261
Affected Ref: 1261
Code:
// SELECT ITEMS IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
$query = "SELECT `ref` FROM `feed` WHERE `ref` NOT IN ($removed)";
echo $query . "<br /><br />";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)):
$ref1 = $row['ref'];
//DELETE ENTRIES IN DATABASE THAT ARE NOT CONTAINED IN CURRENT FEED
$query1 = ("DELETE FROM `feed_property`, `feed_image`, `feed_char` USING `feed_property` INNER JOIN `feed_image` INNER JOIN `feed_char` WHERE feed_property.ref = $ref1 AND feed_image.ref = $ref1 AND feed_char.ref = $ref1 ")or die(mysql_error());
echo "<br />Affected Ref: " . $ref1 ;
echo "<br /><br />";
endwhile;
No errors but the database entries are not deleted.