Return results from DB not in XML Feed

Hi

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.

Help and advice please.

Thanks

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.

Removing the quotes just throws a parameter error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\Websites\solvillas-update\feed-removed.php on line [B]59

59 being: [/B][COLOR=#000000][COLOR=#007700]

while ($row = mysql_fetch_array($result)):

[/COLOR][/COLOR]

The query just seems to fail, it returns all the current entries in the DB whereas I only need it to return those entries NOT contained in the feed.

Thanks to Dennis for looking at this. Anyone else?

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

to this
$removed .= “‘$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;



////////////////////////////////////////////////////////////////////////


?>

Now I have this and all is well.

Cheers guys for your help

I’m glad all is well.

Since you are not using $propertyid for anything else you could remove it and change the $removed to this:
$removed .= “‘$property->id’,”;

I always try not to use variables that are not necessary.

Thanks Tom I have changed accordingly.

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.

Chime away :slight_smile:

OK so now I have this result:

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.

Can someone offer guidance with this please.

Thanks

I think your question is more of MySQL related then PHP. Perhaps repost your question in that section will get the answer. Good luck.

Resolved:



$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());

Thanks for your help