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?

Why `$this->db->where(array('read_date' => NULL));` instead of `$this->db->where('read_date', NULL);` — jtheman, Nov 05 '13 at 14:05
I've tried that also `$this->db->where('read_date', NULL);`. Why I have that is because I read it in this thread: http://stackoverflow.com/questions/2489453/querying-mysql-with-codeigniter-selecting-rows-where-field-is-null — JohnSmith, Nov 05 '13 at 14:08
OK, I see. `$this->db->last_query();` is a great debug tool sometimes — jtheman, Nov 05 '13 at 14:11

score 9 · Answer 1 · answered Nov 05 '13 at 14:17

9

Try this:

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

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

answered Nov 05 '13 at 14:17

Petra

565
1
7
20

Thanks for answer! Returns the same I'm afraid. – JohnSmith Nov 05 '13 at 14:20
1

But that works. ^^ Perhaps poste the created SQL with `$this->db->last_query();` and your 'regular' SQL against it... – Petra Nov 05 '13 at 14:24
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 Nov 05 '13 at 14:30
Now I see the problem. It is `$this->db->count_all_results();` Which doesn't provide correct in my code structure. – JohnSmith Nov 05 '13 at 14:32
Ah sure.. when you use `count` you always get only one row. ^^ – Petra Nov 05 '13 at 14:34

score 1 · Accepted Answer · answered Nov 05 '13 at 14:33

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

score 0 · Answer 3 · answered Nov 05 '13 at 14:39

0

It was the group by in combination with count_all_results(); Found this thread with a solution: http://ellislab.com/forums/viewthread/131724/

answered Nov 05 '13 at 14:39

JohnSmith

417
4
10
21

score 0 · Answer 4 · answered May 07 '14 at 10:25

This is the way of multiple comparison in where command. to check if column data is not null do it like

'sc_id' => TRUE

here is the complete example how I am filter columns in where clause (Codeignitor). The last one show Not NULL Compression

$where = array('somebit' => '1', 'status' => 'Published', 'sc_id' => TRUE );
$this->db->where($where);

score 0 · Answer 5 · answered Feb 05 '16 at 15:57

0

Well, i think another way it could be:

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

Haven't tested this yet, but in theory should work, someone please confirm.

answered Feb 05 '16 at 15:57

César Eduardo Cárdenas Fuentes

1

Value is NULL Codeigniter

5 Answers5