CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordnull
rebellion
rebellion

Reputation: 6740

How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?

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.

Upvotes: 81

Views: 142923

Answers (11)

Raza Rafaideen
Raza Rafaideen

Reputation: 2241

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.

Upvotes: 6

Moonis Abidi
Moonis Abidi

Reputation: 699

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

Upvotes: -1

Pavan K
Pavan K

Reputation: 1

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)

Upvotes: 0

Edmunds22
Edmunds22

Reputation: 791

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

Upvotes: -1

Rodrigo Prazim
Rodrigo Prazim

Reputation: 858

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;

Upvotes: 9

Lirio Push
Lirio Push

Reputation: 29

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

Upvotes: 0

Dave Strickler
Dave Strickler

Reputation: 11

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

Upvotes: 0

Amir Qayyum Khan
Amir Qayyum Khan

Reputation: 473

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

Upvotes: -2

None
None

Reputation: 5649

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

Upvotes: 69

Come2Daddy
Come2Daddy

Reputation: 151

Null must not be set to string...

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

It works properly when null is not wrapped into quotes.

Upvotes: 7

zerkms
zerkms

Reputation: 254916

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

Upvotes: 152

Related Questions