CODEWITHSUNDEEP
phpmysqldatabasearraysnotin
Peter Stuart
Peter Stuart

Reputation: 2444

DELETE FROM NOT IN not working with PHP array

I have an array like this

array([0]=> '[email protected]', [1]=> '[email protected]', [2]=> '[email protected]');

I also have a table with hunders of emails on it.

I want to be able to delete all of these emails apart from the ones that are in the array.

I tried this code but nothing happens:

    $emails = join(', ', $emails);
    $sql = "DELETE FROM emails WHERE customer_id='".$id."' AND email NOT IN(".$emails.")";
    $query = $this->db->query($sql);

Can anyone tell me where I am going wrong?

Thanks

Peter

Upvotes: 1

Views: 2429

Answers (6)

jeeva
jeeva

Reputation: 1603

please try this.

$emails = join(',', $emails);
To avoid comma in last ,
$emails=trim($emails,',');
$sql = "DELETE FROM emails WHERE customer_id='".$id."' AND email NOT IN(".$emails.")";
$res = $this->db->query($sql);

Upvotes: 0

HughieW
HughieW

Reputation: 149

It's more likely that you're not wrapping the individual emails with quotes in the query. mysql_error should have picked that up though surely?

$emails = join('", "', $emails);
$sql = 'DELETE FROM emails WHERE customer_id="'.$id.'" AND email NOT IN("'.$emails.'")';
$query = $this->db->query($sql);

Try echoing out $sql and see what you get (post it here if it doesnt make much sense).

EDIT: How dare 2 people post the same answer as me while I'm typing my answer! >:|

Upvotes: 0

Sepster
Sepster

Reputation: 4849

Your array contains literals, but these aren't each individually being surrounded by quotes for your IN list.

Upvotes: 0

Matt S
Matt S

Reputation: 15374

You're missing quotes:

$emails = implode("', '", $emails);
$sql = "DELETE FROM emails WHERE customer_id='".$id."' AND email NOT IN('".$emails."')";
$query = $this->db->query($sql);

Upvotes: 0

Mihai Iorga
Mihai Iorga

Reputation: 39724

Maybe:

$emails = implode("', '", $emails);
$sql = "DELETE FROM emails WHERE customer_id='".$id."' AND email NOT IN('".$emails."')";
$query = $this->db->query($sql);

Upvotes: 3

Omesh
Omesh

Reputation: 29111

You need to use implode function in php to import covert array to string.

Also need to enclose string values of email in quotes:

$sql = "DELETE FROM emails WHERE customer_id='".$id."' AND 
        email NOT IN('".implode("','",$emails)."')";

Upvotes: 4

Related Questions