How to Select Rows Where Column Value Is Not Null Using Codeigniter's Activerecord

How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?

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

Value is NULL Codeigniter

count_all_results() will replace your whole SELECT clause and the produced query will be this:

SELECT COUNT(*) AS numrows
FROM message
WHERE user_id_to = <your value> AND read_date IS NULL
GROUP BY message_id

... I'm skipping any parenthesis and escape characters of course, but they are irrelevant here.

Just put the whole thing in your select() call:

$this->db->select('id, COUNT(*) as item_count');

Value IS NOT NULL in codeigniter

when you see documentation You can use $this->db->where() with third parameter set to FALSE to not escape your query.
Example:

$this->db->where('field is NOT NULL', NULL, FALSE);

Or you can use custom query string like this

$where = "field is  NOT NULL";
$this->db->where($where);

So your query builder will look like this:

$this->db->select('*');
$this->db->where('field is NOT NULL', NULL, FALSE);
$this->db->get('donors');

OR

$this->db->select('*');
$where = "field is  NOT NULL";
$this->db->where($where);
$this->db->get('donors');

count the not null value using where condition in codeigniter

return $this->museranswer->count_by(array('mcq_id'=>$mcq_id,'length(bookrefrence)>2'));

NULL compare using CodeIgniter ActiveRecord

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

Query in codeigniter WHERE LESS THAN 1 AND NOT NULL result None

you can do this:

function cekSiswaNonKelas(){
        $this->db->select("*");
        $this->db->from("tbsiswa");
        $this->db->where("id_kelas",0);
        $this->db->where("nis!=","");
        $siswaNonKelas = $this->db->get();
        return $siswaNonKelas;
    }

---

Related Topics