IS NOT NULL using database helper

[eluser]Huji[/eluser]
Hi. When I wanted to get the number of rows in a database with their "surname" field still empty, I simply ran:

$query = $this->db->get_where('users', array('surname' => Null));
$data['reg_count'] = $query->num_rows;

Now I want to do the opposite: Get the number of rows that have their surname field filled with text. In terms of SQL syntax, the above example procudes "SELECT * FROM (`users`) WHERE `surname` IS NULL". What I want now is something like "SELECT * FROM (`users`) WHERE `surname` IS NOT NULL". However, I want to use database helper for that rather than hardcoding it myself. How can I do that?

[eluser]Huji[/eluser]
Any comments?

[eluser]Nicholai[/eluser]
Set a variable and write the string, then use active record for the rest. Here's an example from the User Guide:

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

[eluser]Phil Sturgeon[/eluser]

Code:

$this->db->where('surname IS NOT NULL');
$query = $this->db->get('users');
$data[‘reg_count’] = $query->num_rows;

It's possible you could do it this way too:

Code:

$query = $this->db->get_where('users', array('surname !=' => NULL));
$data[‘reg_count’] = $query->num_rows;

[eluser]Huji[/eluser]
narayanis: your comment didn't help.

Phil Sturgeon: Your second code "could" be what I wanted; however, I tested it and it throws an error on the SQL statement.

[eluser]Phil Sturgeon[/eluser]
Did you try the first? And what is the error? Remember to try debugging your SQL before just saying "it doesnt work".

[eluser]Huji[/eluser]
Phil, I'm sure the first solution is working. However, I'm not simply looking for a solution here. I'm looking to see if there is a way to use Database helper to run negative clauses without hard coding them (your first solution hard codes the IS NOT NULL part). If not, I'm going to suggest this feature to be added.

About debuging the SQL statement, I don't think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn't support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?

[eluser]drewbee[/eluser]

Code:

$query = $this->db->get_where('users', array('surname IS NOT' => NULL));

[eluser]Jondolar[/eluser]
The $this->db->select() function will protect your field names with a backtick unless you pass a FALSE as the second parameter. The $this->db->where() and $this->db->get_where() do not so you can pass pretty much any text string as they key in the array. In fact, the docs even show examples where the key has custom operators so drewbee's technique should be valid for future versions as well.

[eluser]Phil Sturgeon[/eluser]
[quote author="Huji" date="1244678382"]About debuging the SQL statement, I don't think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn't support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?[/quote]

How do we know its wrong or how to fix it if you dont show us the query it produces? ;-)