SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 Code:
    <?php
    ////////////////////  DELETE ITEMS REMOVED FROM FEED //////////////////////////


     // LOOP THROUGH ITEM ID'S CURRENTLY IN FEED

    foreach($xml->property as $property) :

    $propertyid $property->id;

    $removed .= sprintf(
        
    "\n%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

  2. #2
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 59

    59 being:
    PHP Code:
    while ($row mysql_fetch_array($result)): 


    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?

  4. #4
    SitePoint Member dennis.g's Avatar
    Join Date
    Jun 2012
    Location
    Athens, Greece
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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());

  5. #5
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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(
    "\n%s,",
    $propertyid


    to this
    $removed .= "'$propertyid',";

  6. #6
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     <?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

  7. #7
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tom8 View Post
    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!!

    PHP Code:
    //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`\n"
                
    "USING `feed_property` INNER JOIN `feed_image` INNER JOIN `feed_char`\n"
                
    "WHERE feed_property.ref = '$ref1'\n"
                
    " AND feed_image.ref = '$ref1'\n"
                
    " 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:
    PHP Code:
    DELETE FROM `feed_property`, `feed_image`, `feed_charUSING `feed_propertyINNER JOIN `feed_imageINNER JOIN `feed_charWHERE 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

  9. #9
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Iím not sure you need those newline (\n) characters. Remove them and see what happen. My knowledge of MySQL is limited. Perhaps members know little more will chime in.

  10. #10
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tom8 View Post
    Perhaps members know little more will chime in.
    Chime away

  11. #11
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP 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

  12. #12
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think your question is more of MySQL related then PHP. Perhaps repost your question in that section will get the answer. Good luck.

    http://www.sitepoint.com/forums/foru...ases-amp-MySQL

  13. #13
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    468
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Resolved:

    PHP Code:

    $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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •