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.

Code: Select all

$array = array(22, 24, 26);

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
8)

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'");