INSERTing a NULL value into a database

[eluser]TheFuzzy0ne[/eluser]
Hi everyone. How do I add a NULL value into a database table? Using PHP's NULL causes an error, using an empty string inserts, well, an empty string, and using 'NULL' inserts a string.

Many thanks in advance.

EDIT: It would appear that I've answered my own question. PHP NULL is converted by the active record class when inserting and updating, however, I don't know how to SELECT a row WHERE a specific field equals NULL. This throws a database error telling me the WHERE clause is invalid

Code:

SELECT *
FROM table
WHERE field !=
LIMIT 1;

[eluser]Dam1an[/eluser]
Have you set it to allow and default to null? Then its a simple case of doing nothingm, is it not?

[eluser]TheFuzzy0ne[/eluser]
Good morning, Dam1an. Yes, I have, but I think I was asking the wrong question. Please see my edit above.

[eluser]rogierb[/eluser]
Hi Fuzzy,

Code:

insert into `table` (fieldname, another_field_name) values (NULL, 'abcd')

Should work if the field is allowed a NULL value.
I don't think this can be doen trough active record, but could be mistaken.


[edit]: ah, post is changed so above doesn't apply

[eluser]rogierb[/eluser]
You cannot use arithmetics with NULL, but you can use 'is'

Code:

select * from table where fieldname is NULL

[eluser]cindor[/eluser]
hi!

you can easily get NULL values by IS_NULL operator in the query like this:

SELECT * FROM table WHERE value IS NULL

edit: ahh damn a little late

[eluser]Colin Williams[/eluser]
... which should look like

Code:

$this->db->where('field IS', NULL)

And if that doesn't work out, AR needs an update.

[eluser]cindor[/eluser]
i guess you can write like this

Code:

$this->db->where->('field is NULL')

[eluser]TheFuzzy0ne[/eluser]
Fantastic. Thanks a lot everyone.