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.

Solution 1

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

Solution 2

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

Solution 3

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;

Solution 4

Null must not be set to string...

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

It works properly when null is not wrapped into quotes.

Solution 5

Much better to use following:

For is not null:

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

For is null:

where('archived', null);

View more solutions

Comments

[Ben Rogmans]

Please note that when you set this third parameter to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

[andyface]

@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. ellislab.com/forums/viewthread/119444/#593454 - gives more info that I ever could.

[Highly Irregular]

-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...

[Thomas Daugaard]

+1 because where('archived IS NOT NULL') this still protects identifiers and accepted answer doesn't.

[Andrey]

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.

[None]

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.

[AndFisher]

OP is looking for IS NOT NULL not IS NULL

[mickmackusa]

This is the correct answer to a different question.

[mickmackusa]

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.