MySQL query using CodeIgniter, selecting rows where field is NULL

Question

I am using the ActiveX class of the CodeIgniter class to query a MySQL database. I need to select rows in a table where the field is not set to NULL:

$this->db->where('archived !=', 'NULL'); $q = $this->db->get('projects'); 

This returns only this query:

 SELECT * FROM projects WHERE archived != 'NULL'; 

The archived field is the DATE field.

Is there a better way to solve this problem? I know that I can just write the query myself, but I will not stick to Active Record in all my code.

Answer 1

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

Answer 2

An active recording definitely has some quirks. When you pass an array to the function $this->db->where() , it will generate IS NULL. For example:

 $this->db->where(array('archived' => NULL)); 

produces

 WHERE `archived` IS NULL 

The quirk is that for a negative IS NOT NULL there is no equivalent. However, there is a way to do this, which gives the correct result and still eludes the operator:

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

produces

 WHERE `archived` IS NOT NULL 

Answer 3

Null should not be set to a string ...

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

It works correctly when null is not quoted.

Answer 4

CodeIgniter 3

only

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

Generated Request:

 WHERE archived IS NOT NULL; 

$ this-> db-> where ('archived IS NOT NULL', null , false ); & L; <Not required

Inverse:

 $this->db->where('archived'); 

Generated Request:

 WHERE archived IS NULL; 

Answer 5

It is much better to use the following. The value is not null.

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

For is null

where ('archived', null);

Answer 6

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

Answer 7

Codeigniter generates an "IS NULL" request, simply leaving the call without parameters:

 $this->db->where('column'); 

Generated Request:

 WHERE `column` IS NULL 

Answer 8

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

Answer 9

You can do (if you want to check for NULL)

 $this->db->where_exec('archived IS NULL) 

If you want to check NOT NULL

 $this->db->where_exec('archived IS NOT NULL) 

Answer 10

One way to check any column is null or not

 $this->db->where('archived => TRUE); $q = $this->db->get('projects'); 

in php, if a column has data, it can be represented as True, otherwise False To use several comparisons in the where command and check if the column data is not, do it like

here is a complete example of how i am the filter columns in where where (Codeignitor). Last Shows Non Null Compression

 $where = array('somebit' => '1', 'status' => 'Published', 'archived ' => TRUE ); $this->db->where($where);