Delete loop not working

[GalaxyTramp]

I am trying to delete records across 3 tables but my code does not work.

 

$query = ("SELECT `propertyref` FROM `feed_property` WHERE `status` = 'Sold' ")or die(mysql_error());
   $result = mysql_query($query);
   
if (isset($result)) {   
     while ($row = mysql_fetch_array($result)):
     $ref1 = mysql_real_escape_string($row['propertyref']);
  
    
$query1 = ("DELETE FROM `feed_property`, `feed_images`, `feed_characteristics`\n"
    . "USING `feed_property` INNER JOIN `feed_images` INNER JOIN `feed_characteristics`\n"
    . "WHERE feed_property.propertyref = '$ref1'\n"
    . " AND feed_images.propertyref = feed_property.propertyref\n"
    . " AND feed_characteristics.propertyref = feed_property.propertyref; ")or die(mysql_error());
    
    
    
    

echo $query1;
endwhile;


    
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>';
}
}

 

 

Query1 echos as:

 

DELETE FROM `feed_property`, `feed_images`, `feed_characteristics` USING `feed_property` INNER JOIN `feed_images` INNER JOIN `feed_characteristics` WHERE feed_property.propertyref = 'abc1' AND feed_images.propertyref = feed_property.propertyref AND feed_characteristics.propertyref = feed_property.propertyref; 
DELETE FROM `feed_property`, `feed_images`, `feed_characteristics` USING `feed_property` INNER JOIN `feed_images` INNER JOIN `feed_characteristics` WHERE feed_property.propertyref = 'abc2' AND feed_images.propertyref = feed_property.propertyref AND feed_characteristics.propertyref = feed_property.propertyref;

 

The query completes without errors but does not delete the entries in the DB. If I run the query singly in PHP My Admin it functions correctly.

 

thanks for your help

 

 

 

 

[xangelo]

As per your code, you're not calling mysql_query properly and you're not calling it in the right spot. Since you want it to execute for each loop, it should be more like this:

 

$query = ("SELECT `propertyref` FROM `feed_property` WHERE `status` = 'Sold' ")or die(mysql_error());
$result = mysql_query($query);

if (isset($result)) {
    while ($row = mysql_fetch_array($result)) {
        $ref1 = mysql_real_escape_string($row['propertyref']);


        $query1 = "DELETE FROM `feed_property`, `feed_images`, `feed_characteristics`\n"
            . "USING `feed_property` INNER JOIN `feed_images` INNER JOIN `feed_characteristics`\n"
            . "WHERE feed_property.propertyref = '$ref1'\n"
            . " AND feed_images.propertyref = feed_property.propertyref\n"
            . " AND feed_characteristics.propertyref = feed_property.propertyref; ";


        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>';
        }


        echo $query1;
    }

}

[GalaxyTramp]

DOH!!

 

Your code works perfectly thanks

 

 

[xangelo]

Not a problem, glad to be of help.