Delete from DB not working correctly

PHP
1

Hi I have the following which deletes from feed_images and feed_characteristics OK but not from feed_item.Can someone put fresh eyes on this and tell me where the problem lies!

//DELETE SOLD OUT OF STOCK ITEMS 

function remove_item($db) {
$sql = "SELECT `item-ref` FROM `feed_item` WHERE `status` = 'Sold' OR `status` = 'Out Stock' ";
$result = $db->query($sql);


if (isset($result)):
    while ($row = $result->fetch_array(MYSQLI_ASSOC)):
        $ref1 = $db->real_escape_string($row['item-ref']);

        $query1 = "DELETE FROM `feed_item`, `feed_images`, `feed_characteristics`\n"
        . "USING `feed_item` INNER JOIN `feed_images` INNER JOIN `feed_characteristics`\n"
        . "WHERE feed_item.item-ref = '$ref1'\n"
        . " AND feed_images.item-ref = feed_item.item-ref\n"
        . " AND feed_characteristics.item-ref = feed_item.item-ref; ";
        
        $db->query($query1);
              
    endwhile;

endif;

  return TRUE;

}

Thanks for looking

2

Reset DBA control…it will fix the problem…

3

Can you explain further as to why this will resolve the problem?

4

I would still like to know what “reset DBA control” will do to resolve this problem. Any further suggestions as to the validity of my coding would be appreciated.

Regards
Colin

5

It’s the USING clause that you have there that is exempting feed_item rows from being deleted.

That being said, there’s no need to run two queries and certainly you should avoid running the delete query in a loop.

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

I think that’s what you’re after (but take a backup, mmkay? :wink: )

6

Thanks for the reply.

feed_item, feed_images and feed_characteristics are 3 separate tables with a common reference being the item-ref. When an item is sold or out of stock all data related to that reference needs to be deleted from all 3 tables.

7

Yes, that’s what I thought - the query that I gave you above (now that I fixed the typos in it) should do what you want.

8

By that I mean, your function can be just

function remove_item($db) {

  $query = "
    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')";

    $db->query($query);
}

Without the need for the initial select query or the while loop.

9

Are you using the old mysql_* extension? if you are you need to be aware that it’s deprecated as of version 5.5 of PHP and is being removed from version 7 of PHP. You should really be using prepared statements instead of any real_escape_string functions

10

The fact that $db appears to be an object (i.e. $db->query()) would suggest that this isn’t the old mysql_ function stuff, I think.

And there isn’t actually any data to “prepare” in Colin’s query. Otherwise, quite right about favouring prepared statements generally.

EDIT - Ugh, sorry @SpacePhoenix - I see what you’re driving at now. But the process that Colin’s after can still be done in a single query without the need for a loop. And it looks like it’s mysqli being used.

Think I should step away from the keyboard - it’s been a long day :wink:

11

Yes to Mysqli guys, currently! I need to rewrite this whole function at some point but need to get it working in the interim.

RavenVelvet your updated delete query gives an error:

Error
There was an error deleting property Not much information there I am afraid. Nothing in the log.

12

That’s odd - I’m struggling to see an error in the query :-/

If you swapped the first part of the delete query for a "select * " does it at least give you the rows that you’re interested in.

i.e.

SELECT *
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')

?

13

Try removing the "\n"s from the query, MySQL probably isn’t liking them being there

14

No luck with that

15

End of a long day so I will take another look at this tomorrow. Thanks for your input so far.

16

That sounds like you’re passing an array instead of a string

17

Just after you place the query into $query1 but before you run it, echo the query, what is displayed by the echo?

1 Like
18

Also try to run the query in PHPMyAdmin or something like that, and see if it works.

19

MySQL returned an empty result set (i.e. zero rows).

20

Is that from running the select statement that I gave you @ColinHughes ?