Deleting multiple rows in SQL/codeigniter

Question

This doesnt seem to work:

 /// delete emails first
         $this->db->where('DateSent', 'DateSent!=NULL');
        $this->db->delete('Emails');

I guess I need to use a loop. Not sure how to go about doing this. Can anyone help?

Cheers

score 4 · Accepted Answer · answered Aug 30 '12 at 14:30

4

If you were to look in the very useful CodeIgniter userguide you'll see the operator should be included as part of the first parameter not second. This should solve it:

$this->db->where('DateSent !=', 'NULL');
$this->db->delete('Emails');

answered Aug 30 '12 at 14:30

Josh Holloway

1,683
2
13
14

Didn't work: A Database Error Occurred Error Number: Incorrect syntax near '='. DELETE FROM Emails WHERE DateSent != – thejoker Aug 30 '12 at 14:37
Sorry had a typo... I've updated it to include single quotes around the NULL – Josh Holloway Aug 30 '12 at 14:41

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

Try this:

$this->db->query('
    DELETE FROM `Emails`
    WHERE id NOT IN (
        SELECT id
        FROM (
            SELECT id
            FROM `Emails`
            WHERE `Emails`.`DateSent` = NULL
        ) foo
    );
);

Query taken from SQL query: Delete all records from the table except latest N? then modified

edited May 23 '17 at 10:28

Community

1
1

answered Aug 30 '12 at 14:30

Sergey Telshevsky

12,077
6
55
78

score 0 · Answer 3 · answered Sep 03 '12 at 07:37

First you need to store the selected values into an array say "$checked"

$checked = implode(',',$checked);     //then it looks array('id1','id2','id3',..);

then you can delete as

$this->db->where("iUserId in",$checked);

$this->db->delete('my_table');

that's it.it works for me.i hope it useful for you.accept if its useful

Deleting multiple rows in SQL/codeigniter

3 Answers3