Thread

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?

Any comments?

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

$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:

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

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.

Did you try the first? And what is the error? Remember to try debugging your SQL before just saying “it doesnt work”.

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?

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.

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?

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

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?

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

Yup. This one is chalked up to user error.

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

This doesn’t work either. The SQL statement it generates is like:

SELECT * FROM `users` WHERE `surname` IS NOT

The “NULL” part is missing.

That is what I get for assuming. Huji is correct in this one gentlemen.

You can see the check in the function _where in the db_active_record.php file.

if (is_null($v) && ! $this->_has_operator($k))
            {
                // value appears not to have been set, assign the test to IS NULL
                $k .= ' IS NULL';
            }

So… this works:

$where = array('forum_id' => null);
        $this->db->where($where, FALSE);        
        $this->db->get('forum');

Outputs : SELECT * FROM (`forum`) WHERE `forum_id` IS NULL

We can do the positive check, however we cannot do the negative

None of the following work:

$where = array('forum_id IS NOT' => null);
$where = array('forum_id !=' => null);

After digging through active record, and finding the following function _has_operator in DB_driver.php the function called above uses this:

function _has_operator($str)
    {
        $str = trim($str);
        if ( ! preg_match("/(\s|<|>|!|=|is null|is not null)/i", $str))
        {
            return FALSE;
        }

        return TRUE;
    }

To properly check against not null, we do the following, and can also do the following for IS NULL as well:

$where = array('forum_id IS NULL' => null,
               'forum_id IS NOT NULL' => null);
$this->db->get('forum');

Outputs: SELECT * FROM (`forum`) WHERE `forum_id` IS NULL AND `forum_id` IS NOT NULL

Granted, I would expect it to more work like this:

$where = array('forum_id IS' => null,
               'forum_id IS NOT' => null);

You would simply have to change the code in _has_operator and _where accordingly.

lol nice one drewbee…

I know you are helping that guy out because he wants to use the AR helper…

But wouldn’t it be easier to just use:

$query = $this->db->query("SELECT ...");

AR helper only makes the code less readable, more complicated and slower…

I would disagree that it makes it slower. It is merely a query builder, and does very little analysis on it.  I personally think the AR helper is a godsend to PHP querying. Query syntax can get very, very ugly if there are a lot of conditionals, and I always prefer coldfusion over php when it comes to writing queries, but that was before CI & AR.

I think it makes it far superior in terms of readability and am willing to take whatever micro hit on speed that it does any day.