Deleting rows not in an array

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Deleting rows not in an array

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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"
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

If it doesn't, just explode the array into a string :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Okay the array is actually

array([0] => 22, [1] => 24, [2] => 26);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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);
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

no need for a loop, you can use implode() to create the string
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Make sure to sanitize the array's content before using it though.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

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