Querying MySQL with CodeIgniter, selecting rows wh

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 wan't to stick with the Active Record throughout my code.

Answer 1

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

Answer 2

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

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

Answer 4

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

Answer 5

Much better to use following For is not null

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

For is null

where('archived', null);

Answer 6

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