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

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

Answer 2

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

Answer 3

Null must not be set to string...

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

It works properly when null is not wrapped into quotes.