Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 907
rebellion Avatar asked Mar 22 '10 00:03

rebellion


People also ask

How do I select non null rows?

Below is the syntax to filter the rows without a null value in a specified column. Syntax: SELECT * FROM <table_name> WHERE <column_name> IS NOT NULL; Example: SELECT * FROM demo_orders WHERE ORDER_DATE IS NOT NULL; --Will output the rows consisting of non null order_date values.

Is null in CI?

Value IS NOT NULL in codeigniter.


2 Answers

where('archived IS NOT NULL', null, false) 
like image 52
zerkms Avatar answered Sep 24 '22 16:09

zerkms


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 
like image 30
None Avatar answered Sep 21 '22 16:09

None