OK so looking further into this, the delete function is included within a loop that inserts data from an XML file using simpleXML. This inclusion would seem unnecessary as it could be run one time after all the inserts/updates are done providing the query could be written to include all those items that are sold or out of stock.
The following code returns 0 deleted rows
try
{
$stmt = $dbh->prepare("DELETE `feed_item`,
`feed_images`,
`feed_characteristics`
FROM `feed_item`
INNER JOIN `feed_images` ON (feed_images.`item-ref` = feed_item.`item-ref`)
INNER JOIN `feed_characteristics` ON (feed_characteristics.`item-ref` = feed_item.`item-ref`)
WHERE feed_item.status IN ('Sold', 'Out Stock')");
$stmt->execute();
} catch (PDOException $e) {
print "Error deleting FROM database!: " . $e->getMessage() . "<br/>";
die();
}
$count = $stmt->rowCount();
echo("Deleted $count rows");
Certainly you shouldnât be running queries inside a loop.
Clearly weâre not matching any rows here - the requirement for that query is that a record exists in all three tables simultaneously and that the status in the feed_item table is either Sold or Out Stock.
Even after it has run, are you still able to confirm that there are rows in the database that should have been deleted?
SELECT * FROM feed_item WHERE status in ('Sold', 'Out Stock');
If that gives any results, can you confirm that each row returned has a corresponding record in both of the other tables?
If the other two tables are optional, youâd need to change the inner joins to left joins.
I have emptied all 3 tables and created a 1 item only XML file. This was inserted into all 3 tables afresh. I then changed the status to sold and run the delete query again and I am pleased, but rather embarassed, to say the query worked.
There was obviously some orphaned data in there which would not delete because it was not contained in all 3 tables as you pointed out. I now need to rewrite this whole project using PDO and address the looping queries.