Value is NULL Codeigniter

Question

Im trying to create the following statement (which works):

SELECT id, COUNT(*) AS item_count FROM message WHERE user_id_to = '1' AND read_date IS NULL GROUP BY message_id

With Codeigniters Active Record. My code looks like this:

$this->db->select('id');
$this->db->from('message');
$this->db->where('user_id_to', $this->session->userdata('id'));
$this->db->where(array('read_date' => NULL));
$this->db->group_by('message_id');
echo $this->db->count_all_results();

I have checked so $this->session->userdata('id') outputs the same ID as my "regular" SQL-statement and it is correct.

The strange thing is that my "regular" statement returns 2, which is right. But my Codeigniter statmenet returns 3, which is obviously wrong.

What am I doing wrong?

Answer 1

Try this:

$this->db->where('read_date IS NULL', null, false);

The third parameter tells him not to escape the clause...

Answer 2

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