Codeigniter active record where is not working

Question

I am running CodeIgniter, using active records. Below is my table structure:

id (int)   user (int)   is_complete (tinyint)
------------------------------------------------
1          24           1
2          24           1
3          24           NULL
4          24           0
5          24           0

Case 1

$this->db->where('user', 24);

Query:

SELECT * FROM `table` WHERE `user` = 24

Works and returns:

id   user   is_complete
--------------------------
1    24     1
2    24     1
3    24     NULL
4    24     0
5    24     0

Case 2

$this->db->where('user', 24);
$this->db->where('is_complete', 1);

Query:

SELECT * FROM `table` WHERE `user` = 24 AND `is_complete` = 1

Works and returns:

id   user   is_complete
--------------------------
1    24     1
2    24     1

Case 3

$this->db->where('user', 24);
$this->db->where('is_complete !=', 1);

Query:

SELECT * FROM `table` WHERE `user` = 24 AND `is_complete` != 1

Does not work and is returning:

id   user   is_complete
--------------------------
4    24     0
5    24     0 

Case 4

$this->db->where('user', 24);
$this->db->where('is_complete <>', 1);

Query:

SELECT * FROM `table` WHERE `user` = 24 AND `is_complete` <> 1

Does not work and is returning:

id   user   is_complete
--------------------------
4    24     0
5    24     0 

Result Needed

It should return:

id   user   is_complete
--------------------------
3    24     NULL
4    24     0
5    24     0

Am I doing something wrong using the where() method, or is there a better way to accomplish this?

Answer 1

This is an issue at the database level, though it is not a bug - this is how SQL works with null values. This query:

SELECT
    *
FROM
    `table`
WHERE
    `user` = 24 AND `is_complete` != 1

will return records where is_complete is not equal to 1, but not null. If you wish to include null records as well, you will need to do this:

SELECT
    *
FROM
    `table`
WHERE
    `user` = 24 AND
    (`is_complete` != 1 OR `is_complete` IS NULL)

By comparing a column with a non-null value, you've automatically excluded nulls, which need to be treated with a different syntax.

You'll need to add in an additional, bracketed clause for the query; see here for how.