How to SELECT rows WHERE column value IS NOT NULL using CodeIgniter's query builder

Question

I'm using CodeIgniter's query builder 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 query builder methods throughout my code.

zerkms · Accepted Answer · 2010-03-22 01:01:27Z

153

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

answered Mar 22, 2010 at 1:01

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Rogmans Over a year ago

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

Andrey Over a year ago

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 · Accepted Answer · 2013-09-09 17:29:39Z

70

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 9, 2013 at 17:29

answered Dec 21, 2012 at 19:15

None

5,6911 gold badge43 silver badges53 bronze badges

Comments

Rodrigo Prazim · Accepted Answer · 2016-07-25 08:26:58Z

9

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;

answered Jul 25, 2016 at 8:26

Rodrigo Prazim

8688 silver badges15 bronze badges

Comments

GusDeCooL · Accepted Answer · 2012-11-27 17:00:41Z

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, 2012 at 17:00

GusDeCooL

5,78119 gold badges71 silver badges103 bronze badges

answered Feb 23, 2012 at 18:09

Come2Daddy

1511 silver badge1 bronze badge

3 Comments

andyface Over a year ago

@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 Over a year ago

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

mickmackusa Jul 20 at 11:07

This answer never told you to use the erroneous code $this->db->where('when_removed is', null);. Your -1 is not deserved. You may find this reference helpful though: stackoverflow.com/a/64868052/2943403

Raza Rafaideen · Accepted Answer · 2023-11-20 08:18:02Z

6

Much better to use following:

For is not null:

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

For is null:

where('archived', null);

update

We can use without second parameter for where in CodeIgniter's Active Record is not needed when checking for NULL or IS NOT NULL. Here's the correct usage:

For IS NOT NULL:

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

For IS NULL:

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

In both cases, you don't need to provide a second parameter. The where method automatically handles NULL comparisons correctly without the need for an explicit second parameter.

edited Nov 20, 2023 at 8:18

answered Oct 24, 2014 at 11:40

Raza Rafaideen

2,2511 gold badge22 silver badges30 bronze badges

Comments

gunr2171 · Accepted Answer · 2014-11-03 20:22:59Z

0

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

edited Nov 3, 2014 at 20:22

gunr2171

17.7k26 gold badges68 silver badges102 bronze badges

answered Nov 3, 2014 at 20:02

Dave Strickler

111 bronze badge

1 Comment

mickmackusa May 29 at 23:25

For anyone caught off-guard: PSQL, Oracle, and IBM Db2, SAP HANA, Vertica, etc. do not have ISNULL so this is not a dialect-agnostic solution.

mickmackusa · Accepted Answer · 2025-11-17 12:40:39Z

Similar to my chart of IS NULL expressions for CodeIgniter's query builder methods, below is a battery of good, bad, and ugly IS NOT NULL expressions.

You may be surprised to see that ->where('column is not ') works perfectly, but simply removing the trailing space renders a completely unsuitable expression.

Perhaps more surprisingly, ->where('column !=') is magically rendered as `column` IS NOT NULL.

Quality	Syntax	SQL
⭐	`->where('column is not null')`	`column` is not null
✅	`->where('column !=')`	`column` IS NOT NULL
✅	`->where('column !=', null)`	`column` IS NOT NULL
✅	`->where('column is not ')`	`column` IS NOT NULL
✅	`->where('column is not null', null)`	`column` is not null
✅	`->where(['column is not ' => null])`	`column` IS NOT NULL
✅	`->where(['column is not null' => null])`	`column` is not null
⚠️	`->where('column is not null', null, false)`	`column is not null`
⚠️	`->where('column is not', 'null', false)`	`column is not null`
⚠️	`->where('column is not ', null, false)`	`column IS NOT NULL`
❌	`->where('column is not')`	`column is not`
❌	`->where('column is not', 'null')`	`column is not 'null'`
❌	`->where('column is not', null, false)`	`column is not`
❌	`->where('column is not null', 'null')`	`column` is not null 'null'
❌	`->where('column', 'is not null')`	`column` = 'is not null'
❌	`->where('column', 'is not null', false)`	`column = is not null`
❌	`->where('NOT column ')`	`NOT column`
❌	`->where('NOT column IS', null)`	`NOT column IS`
❌	`->where('NOT column <=>')`	`NOT` `column` <= `>`
❌	`->where(['col is not' => null])`	`col is not`
❌	`->where(['col is not' => null], null, false)`	`col is not`

_{⭐ is my recommendation, ✅ means correct and fully quoted, ⚠️ means it will work but not fully quoted, ❌ means it is incorrect}

When using get_where(), the second parameter can be a string or an associative array, but you cannot express escape = false.

Collectives™ on Stack Overflow

How to SELECT rows WHERE column value IS NOT NULL using CodeIgniter's query builder

7 Answers 7

2 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related