How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?
PhpMysqlCodeigniterActiverecordNullPhp Problem Overview
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.
Php Solutions
Solution 1 - Php
where('archived IS NOT NULL', null, false)
Solution 2 - Php
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 - Php
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 - Php
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 - Php
Much better to use following:
For is not null:
where('archived IS NOT NULL', null);
For is null:
where('archived', null);
Solution 6 - Php
And just to give you yet another option, you can use NOT ISNULL(archived) as your WHERE filter.
Solution 7 - Php
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
Solution 8 - Php
$this->db->or_where('end_date IS', 'NULL', false);
Solution 9 - Php
You can do (if you want to test NULL)
$this->db->where_exec('archived IS NULL)
If you want to test NOT NULL
$this->db->where_exec('archived IS NOT NULL)
Solution 10 - Php
If you are using multi where in your model like:
function getCommonRecords($id, $tbl_name, $multi_where='') {
$this->db->select('*');
$this->db->where('isDeleted', '0');
if ($id > 0) {
$this->db->where('id', $id);
}
if ($multi_where != '') {
foreach ($multi_where as $key => $val) {
$this->db->where($key, $val);
}
}
$queryResult = $this->db->get($tbl_name);
return $queryResult->result_array();
}
Then I would recommend using the following syntax that will bypass the second parameter in calculating the multi where condition.
$this->api->getCommonRecords(NULL,'contracts', ['id' =>,'party1Sign IS NOT NULL'=>NULL,'party2Sign IS NOT NULL'=>null]);
Solution 11 - Php
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);