So I have a $items= array(13, 19, 22, 3129);
How can I simply not include the rows with these id's in my DELETE query sql?
Add a comment
|
3 Answers
DELETE FROM table1
WHERE id NOT IN (13, 19, 22, 3129)
In PHP, you can use the implode() function to build the IN clause.
$items = implode(",", $items);
$sql = "DELETE FROM table1 WHERE id NOT IN ({$items})";
Comments
Use implode but dont set the same array variable as you might be use it later in your program as also it is not recommended or taken as good programming practice to change the data type of a variable just like that.
$items= array(13, 19, 22, 3129);
$sql = sprintf("delete from tbl where id_col not in (%s)",implode(",", $items));
Comments
If mytable is big but the number of entries to keep is small, here is a faster way
$items= array(13, 19, 22, 3129);
$sql = "CREATE TABLE mynewtable LIKE mytable";
mysql_query($sql);
$id_list = sprintf("(%s)",implode(",", $items));
$sql = "INSERT INTO mynewtable SELECT * FROM mytable WHERE id_col IN " . $id_list;
mysql_query($sql);
$sql = "DROP TABLE mytable";
mysql_query($sql);
$sql = "ALTER TABLE mynewtable RENAME mytable";
mysql_query($sql);
