How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?

Question

I'm using CodeIgniter's Active Record class to query the MySQL database. I need to select the rows in a table where a field is not set to NULL:

$this->db->where('archived !=', 'NULL');
$q = $this->db->get('projects');

That only returns this query:

SELECT * FROM projects WHERE archived != 'NULL';

The archived field is a DATE field.

Is there a better way to solve this? I know I can just write the query myself, but I want to stick with the Active Record throughout my code.

score 152 · Accepted Answer · answered Mar 22 '10 at 01:01

152

where('archived IS NOT NULL', null, false)

answered Mar 22 '10 at 01:01

zerkms

249,484
69
436
539

14

Please note that when you set this third parameter to FALSE, CodeIgniter will not try to protect your field or table names with backticks. – Ben Rogmans Jan 19 '12 at 15:55
12

Also worth adding that you can use this when you're passing an array parameter: `where(array("foo" => "bar", "archived IS NOT NULL" => null))`. Pretty unintuitive but works. – Andrey Nov 28 '13 at 17:05

None · Answer 2 · 2013-09-09T17:29:39.687

69

The Active Record definitely has some quirks. When you pass an array to the $this->db->where() function it will generate an IS NULL. For example:

$this->db->where(array('archived' => NULL));

produces

WHERE `archived` IS NULL

The quirk is that there is no equivalent for the negative IS NOT NULL. There is, however, a way to do it that produces the correct result and still escapes the statement:

$this->db->where('archived IS NOT NULL');

produces

WHERE `archived` IS NOT NULL

edited Sep 09 '13 at 17:29

answered Dec 21 '12 at 19:15

None

5,491
1
40
51

13

+1 because `where('archived IS NOT NULL')` this still protects identifiers and accepted answer doesn't. – Thomas Daugaard Jan 04 '13 at 13:03

score 8 · Answer 3 · answered Jul 25 '16 at 08:26

CodeIgniter 3

Only:

$this->db->where('archived IS NOT NULL');

The generated query is:

WHERE archived IS NOT NULL;

$this->db->where('archived IS NOT NULL',null,false); << Not necessary

Inverse:

$this->db->where('archived');

The generated query is:

WHERE archived IS NULL;

score 7 · Answer 4 · edited Nov 27 '12 at 17:00

7

Null must not be set to string...

$this->db->where('archived IS NOT', null);

It works properly when null is not wrapped into quotes.

edited Nov 27 '12 at 17:00

GusDeCooL

5,639
17
68
102

answered Feb 23 '12 at 18:09

Come2Daddy

151
1
1

3

@GusDeCooL Not sure this actually works. Using this output ..." field IS NOT" without the NULL. The accepted answer seems to be the way to do it properly. http://ellislab.com/forums/viewthread/119444/#593454 - gives more info that I ever could. – andyface Nov 27 '12 at 16:21
7

-1 because it doesn't work! I tried a similar variation of this: $this->db->where('when_removed is', null); gave a database error and showed the query generated included: ...WHERE "when_removed" is ORDER BY "last_name" asc... – Highly Irregular Dec 03 '12 at 21:54

score 5 · Answer 5 · edited May 13 '20 at 07:07

5

Much better to use following:

For is not null:

where('archived IS NOT NULL', null);

For is null:

where('archived', null);

edited May 13 '20 at 07:07

Nimantha

6,405
6
28
69

answered Oct 24 '14 at 11:40

Raza Rafaideen

2,119
1
19
30

score 0 · Answer 6 · edited Nov 03 '14 at 20:22

0

And just to give you yet another option, you can use NOT ISNULL(archived) as your WHERE filter.

edited Nov 03 '14 at 20:22

gunr2171

16,104
25
61
88

answered Nov 03 '14 at 20:02

Dave Strickler

11
1

score 0 · Answer 7 · answered May 07 '15 at 05:31

0

Codeigniter generates an "IS NULL" query by just leaving the call with no parameters:

$this->db->where('column');

The generated query is:

WHERE `column` IS NULL

answered May 07 '15 at 05:31

Lirio Push

29
1

OP is looking for `IS NOT NULL` not `IS NULL` – AndFisher Dec 14 '16 at 12:22
This is the correct answer to a different question. – mickmackusa May 12 '20 at 07:42

score 0 · Answer 8 · answered Aug 03 '18 at 14:31

0

$this->db->or_where('end_date IS', 'NULL', false);

answered Aug 03 '18 at 14:31

Edmunds22

715
9
10

score 0 · Answer 9 · answered Aug 08 '19 at 14:05

0

You can do (if you want to test NULL)

$this->db->where_exec('archived IS NULL)

If you want to test NOT NULL

$this->db->where_exec('archived IS NOT NULL)

answered Aug 08 '19 at 14:05

Pavan K

1
1

1

Where are you finding this `where_exec()` method? I don't see it anywhere in my CI project and I can't find a single resource online that speaks of its name. Please support this answer with a link to documentation. – mickmackusa May 12 '20 at 23:19

score 0 · Answer 10 · answered Sep 09 '21 at 12:44

If you are using multi where in your model like:

function getCommonRecords($id, $tbl_name, $multi_where='') {
    $this->db->select('*');
    $this->db->where('isDeleted', '0');
    if ($id > 0) {
        $this->db->where('id', $id);
    }
    if ($multi_where != '') {
        foreach ($multi_where as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    $queryResult = $this->db->get($tbl_name);
    return $queryResult->result_array();
}

Then I would recommend using the following syntax that will bypass the second parameter in calculating the multi where condition.

 $this->api->getCommonRecords(NULL,'contracts', ['id' =>,'party1Sign IS NOT NULL'=>NULL,'party2Sign IS NOT NULL'=>null]);

Amir Qayyum Khan · Answer 11 · 2014-09-14T06:23:36.007

-2

One way to check either column is null or not is

$this->db->where('archived => TRUE);
$q = $this->db->get('projects');

in php if column has data, it can be represent as True otherwise False To use multiple comparison in where command and to check if column data is not null do it like

here is the complete example how I am filter columns in where clause (Codeignitor). The last one show Not NULL Compression

$where = array('somebit' => '1', 'status' => 'Published', 'archived ' => TRUE );
$this->db->where($where);

edited Sep 14 '14 at 06:23

answered May 07 '14 at 10:26

Amir Qayyum Khan

473
2
15

I just tried this and output the SQL it generates: `SELECT * FROM (\`test\`) WHERE \`somebit\` = '1' AND \`status\` = 'Published' AND \`sc_id\` = 1`. Checking if a column is equal to 1 and checking if a column is not null are extremely different. – None May 27 '14 at 15:18

How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?

11 Answers11

Linked

Related