Delete NOT IN an array deletes all the rows, PHP Mysql

Question

I am trying to accomplish delete all the rows of a table where event id is equal to some value(unique key) and ids not in my array;

So lets say event_id=5 has 4 rows(1,2,3,4) and I my array has (1,2) I want to delete 3,4 where event id is equal to 5.

To do that: ->Select ID from an array and put id's to another array (seems working)

->Delete all the rows except the one comes with select query(fails deletes all the rows of the table).

$query = "SELECT file_id FROM FILES WHERE event_id=$event_id AND name IN ('$names')";
    $result = $sql->query($query); 
    //printf("$query: %s\n", $query);
    var_dump($query);    
    //printf("\n");
    if (!$result) {
        var_dump($result);
        printf("Query failed: %s\n", $mysqli->error);
        sendResponse(417, json_encode("Query failed"));

    exit;
    }
    //printf("\n");
    $rows = array();
    while($row = $result->fetch_row()) {
                $rows[]=$row;
                printf("\n");
    }

    $result->close();
    var_dump($rows);
    printf("\n");

    $delete = join("', '",$rows);
    var_dump($delete);
    printf("\n");

    //send delete request here
    $query ="DELETE FROM FILES WHERE event_id=$event_id AND file_id NOT IN ('$delete')";
    $result = $sql->query($query);
    //printf("$query: %s\n", $query);
    var_dump($query);
    printf("\n");     
    if (!$result) {
        var_dump($result);
        printf("\n");
        printf("Query failed: %s\n", $mysqli->error);
        sendResponse(417, json_encode("Query failed"));

    exit;
    }

LOG:

string(143) "SELECT file_id FROM FILES WHERE event_id=7 AND name IN ('sample-1.pdf', '2012-lve-vegas-faq.pdf', 'sample-2.pdf', 'sample-3.pdf', 'sample.pdf')" array(5) { [0]=> array(1) { [0]=> string(2) "89" } [1]=> array(1) { [0]=> string(2) "90" } [2]=> array(1) { [0]=> string(2) "91" } [3]=> array(1) { [0]=> string(2) "92" } [4]=> array(1) { [0]=> string(2) "93" } } string(41) "Array', 'Array', 'Array', 'Array', 'Array" string(99) "DELETE FROM FILES WHERE event_id=7 AND file_id NOT IN ('Array', 'Array', 'Array', 'Array', 'Array')"

You can see in the log $delete = join("', '",$rows); causes ('Array', 'Array', 'Array', 'Array', 'Array') is not what I want,It should have been something like ('89','90', '91', '92', '93')

How can I make this work?

Probably it should be $rows[]=$row['file_id']; or $rows[]=$row[0]; (depends on fetch method). — Wrikken
– Wrikken, Commented May 14, 2013 at 17:16
Which database layer are you using? It looks like mysqli, which means you should make use of the escaping facilities it provides because this is terrifyingly buggy code. SQL injection issues can bite you hard. — tadman
– tadman, Commented May 14, 2013 at 17:21
@tadman this is very basic so I can communicate with ios devices, as far as I know there is no way that iphone user can go into sandbox of my app and grab the url/server and try to injections on my server, However what do you exactly suggest security/bug wise? — SpaceDust__
– SpaceDust__, Commented May 14, 2013 at 17:39
It doesn't matter who's interfacing with this, or how they're doing it. These kinds of bugs should never be there in the first place. They lie dormant, and when your use case changes, you could be in for a nasty surprise. Be disciplined about never, ever putting in unescaped user data. You might think it's overblown, just paranoia, but the consequences of a mistake can be severe. — tadman
– tadman, Commented May 14, 2013 at 17:44

Wrikken · Accepted Answer · 2013-05-14 17:18:11Z

7

Probably it should be $rows[]=$row['file_id']; or $rows[]=$row[0]; (depends on fetch method). However, this would be more workable:

 DELETE FROM FILES WHERE event_id=$event_id AND name NOT IN ('$names')

answered May 14, 2013 at 17:18

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

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

1 Comment

Moak Over a year ago

Although the first part is the answer, definitely go with the second part.

li-on · Accepted Answer · 2013-05-14 17:18:09Z

0

Replace

$rows[]=$row;

to

$rows[]=$row['file_id'];

BTW. Never pass variables to query without escaping. Check i.e. AdoDB library

answered May 14, 2013 at 17:18

li-on

5392 silver badges8 bronze badges

3 Comments

tadman Over a year ago

I've never heard of ADOdb prior to a few days ago. Why would you use that over PDO?

li-on Over a year ago

You can use ADOdb instead of PDO (it is more friendly: AutoExecute, GetAll, GetRow, GetOne etc.) or use $sql->quote($var) if you rather want to stay in PDO

Ivan Yonkov Over a year ago

AutoExecute ... hmm... I'd rather use some of the wrappers that any of the major PHP frameworks have. PDO is native PHP, ADOdb is extension. The only advantage seems to be the database specter of supporting

Collectives™ on Stack Overflow

Delete NOT IN an array deletes all the rows, PHP Mysql

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related