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?

Solution 1

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

Solution 2

Try this:

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

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

Comments

[JohnSmith]

Thanks for answer! Returns the same I'm afraid.

[Petra]

But that works. ^^ Perhaps poste the created SQL with $this->db->last_query(); and your 'regular' SQL against it...

[JohnSmith]

I get this when echo'ing last_query(); SELECT COUNT(*) AS numrows` FROM (message) WHERE user_id_to = '1' AND read_date IS NULL GROUP BY message_id

[JohnSmith]

Now I see the problem. It is $this->db->count_all_results(); Which doesn't provide correct in my code structure.

[Petra]

Ah sure.. when you use count you always get only one row. ^^