Page 1 of 1
Deleting rows not in an array
Posted: Thu Jun 28, 2007 11:57 am
by Smasher
I have an array with the numbers 22, 24, 26 for example.
I have a table with rows. I would like to delete rows not in the array.
How would I achieve this?
Posted: Thu Jun 28, 2007 12:08 pm
by volka
Code: Select all
DELETE .... WHERE xyz NOT IN (22,24,26)
If you're using php to build the query you might tbe interested in
http://de2.php.net/join
Posted: Thu Jun 28, 2007 1:18 pm
by Smasher
Hello! I am using PHP and I should have stated, with ids not in the array.
Would the following work?
$array = (22,24,26);
"DELETE FROM DB.TABLE WHERE id NOT IN $array"
Posted: Thu Jun 28, 2007 3:06 pm
by ReverendDexter
If it doesn't, just explode the array into a string

Posted: Thu Jun 28, 2007 3:21 pm
by feyd
Smasher wrote:Hello! I am using PHP and I should have stated, with ids not in the array.
Would the following work?
$array = (22,24,26);
"DELETE FROM DB.TABLE WHERE id NOT IN $array"
The array assignment isn't valid syntax, so no.
Posted: Fri Jun 29, 2007 4:22 am
by Smasher
Okay the array is actually
array([0] => 22, [1] => 24, [2] => 26);
Posted: Fri Jun 29, 2007 4:26 am
by superdezign
Smasher wrote:Okay the array is actually
array([0] => 22, [1] => 24, [2] => 26);
That's... not quite correct either. I believe it will create associative entries that include brackets. If not and I'm somehow mistaken, it's still redundant.
Posted: Fri Jun 29, 2007 4:36 am
by Smasher
Its from a list field thats been $_POST.
When I print_r the $_POST['segments'] (field name) I get what I posted.
When foreaching through I get the results I want, I guess the way todo it is build a string.
Posted: Fri Jun 29, 2007 4:58 am
by Smasher
Using the following.
Code: Select all
$string = '';
foreach($segments as $segment) {
$string .= ", $segment";
}
$db->query("DELETE FROM ens.my_table WHERE `id` NOT IN (".substr($string, 2).") AND `some_id` = '$someId'");
Worked fine

Thanks for your help once again

Posted: Fri Jun 29, 2007 5:11 am
by John Cartwright
no need for a loop, you can use implode() to create the string
Posted: Fri Jun 29, 2007 6:57 am
by feyd
Make sure to sanitize the array's content before using it though.
Posted: Fri Jun 29, 2007 8:00 am
by Smasher
Jcart wrote:no need for a loop, you can use implode() to create the string
Howso?
Code: Select all
$segments = implode(",", $segments);
$db->query("DELETE FROM ens.my_table WHERE `id` NOT IN ($segments) AND `some_id` = '$someId'");