NULL compare using CodeIgniter ActiveRecord [duplicate]

Question

I've got a table like this.

[id]     [name]     [age]
1        foo        20
2        bar        NULL

If i need to return all the rows when age's fields is NULL i do the following and it works fine.

$this->db->from('person')->where('age', null);

The problem is that i need to do the opposite, i mean, return all rows when the age fields are not null values.
In SQL i do something like this

SELECT * FROM person WHERE age is not null

How do i do this using CodeIgniter ActiveRecord ??

Any help appreciated.

hope this will help codeigniter.com/forums/viewthread/119444/P15 — Filippo oretti
– Filippo oretti, Commented Nov 22, 2012 at 17:17
are you setting the ALLOW NULL DEFAULT NULL mysql options to your db fields? — Filippo oretti
– Filippo oretti, Commented Nov 22, 2012 at 17:17
This is the way of multiple comparison in where command $where = array('somebit' => '1', 'status' => 'Published', 'sc_id' => TRUE ); $this->db->where($where); — Amir Qayyum Khan
– Amir Qayyum Khan, Commented May 7, 2014 at 10:22
The thread referenced above by @sbaaaang is now at ellislab.com/forums/viewthread/119444/P15 — Iain
– Iain, Commented Dec 2, 2014 at 15:12

alphacentauri · Accepted Answer · 2012-11-23 11:15:02Z

You may use:

$this->db->from('person')->where('age is not null');

If you curious how this happens see system/database/DB_active_rec.php

protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
{
    ...

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

        ...
}

And system/database/DB_driver.php

/**
 * Tests whether the string has an SQL operator
 *
 * @access  private
 * @param   string
 * @return  bool
 */
function _has_operator($str)
{
    $str = trim($str);
    if ( ! preg_match("/(\s|<|>|!|=|is null|is not null)/i", $str))
    {
        return FALSE;
    }

    return TRUE;
}

As a result you could pass just first parameter to where() method and if first operator of this method has a sub string is not null it will be left untouched.

To read more - CodeIgniter forum

mallix · Accepted Answer · 2012-11-22 17:12:36Z

-1

This must be the way:

 $this->db->from('person')->where('age IS NOT NULL', null);

edited Nov 22, 2012 at 17:12

answered Nov 22, 2012 at 16:45

mallix

1,4392 gold badges22 silver badges45 bronze badges

4 Comments

Javier Cadiz Over a year ago

I try that and it doesn't work. The ActiveRecord tries to compare the field with NULL, but NULL doesn't compare equal to anything, so, it reports a sql syntax error.

mallix Over a year ago

Try where('age IS NOT NULL', null)

alphacentauri Over a year ago

look at the method signature public function where($key, $value = NULL, $escape = TRUE) - the second parameter in not obligatory.

Javier Cadiz Over a year ago

yes @mallix that works fine but like alphacentauri said, null in that case is not required.

Collectives™ on Stack Overflow

NULL compare using CodeIgniter ActiveRecord [duplicate]

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related