DELETE FROM NOT IN not working with PHP array

Question

I have an array like this

array([0]=> 'email@email.com', [1]=> 'email2@email.com', [2]=> 'email3@email.com');

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

You need to convert array to textual form like @Omesh proposed in his answer — rkosegi, Sep 06 '12 at 13:35
I hope you are certain that the array elements cannot contain SQL. Otherwise, look into [prepared statements](http://stackoverflow.com/a/60496/623041). — eggyal, Sep 06 '12 at 13:55

score 4 · Accepted Answer · answered Sep 06 '12 at 13:34

4

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

answered Sep 06 '12 at 13:34

Omesh

27,801
6
42
51

score 3 · Answer 2 · answered Sep 06 '12 at 13:35

3

Maybe:

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

answered Sep 06 '12 at 13:35

Mihai Iorga

39,330
16
106
107

@PeterStuart The difference is in the `implode` and that it will encapsulate each email address in single quotes nicely. +1 (I read it three times before I noticed the difference) – Fluffeh Sep 06 '12 at 13:37

score 0 · Answer 3 · answered Sep 06 '12 at 13:35

0

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

answered Sep 06 '12 at 13:35

Matt S

14,976
6
57
76

score 0 · Answer 4 · answered Sep 06 '12 at 13:35

0

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

answered Sep 06 '12 at 13:35

Sepster

4,800
20
38

score 0 · Answer 5 · answered Sep 06 '12 at 13:38

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! >:|

score 0 · Answer 6 · answered Sep 06 '12 at 13:44

0

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

answered Sep 06 '12 at 13:44

jeeva

1,573
2
15
24

DELETE FROM NOT IN not working with PHP array

6 Answers6