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
", $query);
var_dump($query);
//printf("
");
if (!$result) {
var_dump($result);
printf("Query failed: %s
", $mysqli->error);
sendResponse(417, json_encode("Query failed"));
exit;
}
//printf("
");
$rows = array();
while($row = $result->fetch_row()) {
$rows[]=$row;
printf("
");
}
$result->close();
var_dump($rows);
printf("
");
$delete = join("', '",$rows);
var_dump($delete);
printf("
");
//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
", $query);
var_dump($query);
printf("
");
if (!$result) {
var_dump($result);
printf("
");
printf("Query failed: %s
", $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?