Trying to delete all rows in database that do not match array

Question

I am trying to figure out how to delete all ids in the database that do not exist in an array. I have been trying to use NOT IN in my query but I am not sure why it wont work when running it in a script the same way it works when I manually enter it into mysql. Here is an example.

mysqli_query($con, "DELETE FROM table WHERE id NOT IN ($array)");

$array is a list of ids from a json api. I use CURL to fetch the ids and I am trying to delete all ids in the database that do not match the ids in $array. First I use another simple CURL script to scrape the apis and insert the ids found into the database and what I am trying to do here is basically make a link/data checker. If the ids in the database are not found in the array when rechecking them then I want them deleted.

I thought that the query above would work perfect but for some reason it doesn't. When the query is ran from a script the mysql log shows the queries being ran as this.

Example:

DELETE FROM table WHERE id NOT IN ('166')

or this when I am testing multiple values.

DELETE FROM table WHERE id NOT IN ('166', '253', '3324')

And what happens is it deletes every row in the table every time. I don't really understand because if I copy/paste the same query from the log and run it manually myself it works perfect.

I have been trying various ways of capturing the array data such as array_column, array_map, array_search and various functions I have found but the end result is always the same. For right now, just for testing I am using these 2 bits of code for testing 2 different apis which gives me the same sql query log output as above. The functions used are just a couple random ones that I found.

//$result is the result from CURL using json_decode


function implode_r($g, $p) {
    return is_array($p) ?
           implode($g, array_map(__FUNCTION__, array_fill(0, count($p), $g), $p)) :
           $p;
}

foreach ($result['data'] as $info){
$ids = implode_r(',', $info['id']);
mysqli_query($con, "DELETE FROM table WHERE id NOT IN ($ids)");
}

And

$arrayLength = count($result);
for($i = 0; $i < $arrayLength; $i++) {
mysqli_query($con, "DELETE FROM table WHERE id NOT IN ('".$result[$i]['id']."')");
}

If anyone knows what is going on i'd appretiate the help or any suggestions on how to achieve the same result. I am using php 7 and mysql 5.7 with innodb tables if that helps.

ArtisticPhoenix · Accepted Answer · 2017-07-18 18:52:08Z

1

It probably doesn't work because your IN value is something like this

IN('1,2,3,4');

When what you want is this

IN('1','2','3','4')

OR

IN( 1,2,3,4)

To get this with implode include the quotes like this

 $in = "'".implode("','", $array)."'";

NOTE whenever directly inputting variables into SQL there is security Implications to consider such as SQLInjection. if the ID's are from a canned source you're probably ok, but I like to sanitize them anyway.

edited Jul 18, 2017 at 18:52

answered Jul 18, 2017 at 18:46

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Greg Over a year ago

I figured out my issue. I already had a piece of code that did exactly what you are saying so I got confused even more. $res = array_map(function($el){ return $el['id']; }, $result); $str = implode("','", $res); It seems my issue this whole time that I couldn't get figured out was that I had a space between IN and (). It didn't occur to me that, that would cause an issue until I seen how you wrote it like this IN(). Things seem to be working working without the space. Thanks.

Greg Over a year ago

I am not sure if the space causes an issue because I have not looked into it yet but things are working.

ArtisticPhoenix Over a year ago

it shouldn't matter IN ( ) and IN() same thing in SQL See > dev.mysql.com/doc/refman/5.7/en/function-resolution.html

colburton · Accepted Answer · 2017-07-18 18:45:22Z

1

You can't mix array and string.

This works:

mysqli_query($con, "DELETE FROM table WHERE id NOT IN (".implode(',', $ids).")");

answered Jul 18, 2017 at 18:45

colburton

4,7252 gold badges28 silver badges41 bronze badges

Collectives™ on Stack Overflow

Trying to delete all rows in database that do not match array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related