Inserting NULL values into MySQL from Codeigniter[SOLVED]

[eluser]comex[/eluser]
Hello,
I've tried asking Dr. Google but he seemed unwilling to assist.
I'm not sure if this issue is related to CI or just to my bad understanding of PHP.

What I've been trying to do is to insert the value NULL into INT fields in MySQL(mysqli driver) but I end up with the value 0 inserted instead. I'm using Codeigniters active record class.
The inserted values are coming from variables, I've tried $a = '', $a='NULL' and $a = NULL.
Inserting NULL directly to MySQL works, so the problem seems to be with how PHP interacts with MySQL.

Anyone who has had this problem before?
Thank you!

[eluser]markup2go[/eluser]
Hi, can you paste your table schema please?

Thanks

[eluser]jedd[/eluser]
[quote author="comex" date="1259365383"]
What I've been trying to do is to insert the value NULL into INT fields in MySQL(mysqli driver) but I end up with the value 0 inserted instead. I'm using Codeigniters active record class.
[/quote]

mysqli? {raises eyebrow}

Show us the CI AR code that you're using - it's much easier for us to work out what's going on when we see what's going on.

Inserting NULL directly to MySQL works, so the problem seems to be with how PHP interacts with MySQL.

Code?

Schema, as suggested, would be handy - you can tell straight away from a DESCRIBE tablename; whether NULL is allowed in that column.

[eluser]comex[/eluser]
"Inserting NULL directly to MySQL works" - quoted from my op.

There is nothing in the schema that prevents setting a column as null, no "not null" declarations.
The tables are defined along the usual line: "CREATE TABLE TABLENAME( ... );
All columns are defined along this line: "ID_PAR INT(5)"

In line with the above quote the following works as desired: "UPDATE TABLENAME SET ID_PAR=NULL;"

Describing the table tells me that it allows null on most columns(the ones I'm interested in NULLing.)

As for 'mysqli', its the 'dbdriver' in database.php.

I am fairly sure that this is not an MySQL-error, I believe the problem lies with how my PHP-code interracts with MySQL.

The PHP-code is worked up in my own little 'framework' so it's hard to post it all at once =)
But it's working for everything but setting NULL. The corresponding code would be:

$this->db->set(array('ID_PAR'=>'NULL'))->where( ... )->update('MYTABLE');
Replacing 'NULL' with NULL or '' produces the same result.

Whats customary to use? 'NULL', NULL, '' or something completely different? =)

[eluser]jedd[/eluser]
Now, can you try this, just out of curiosity.

Code:

$this->db->set('ID_PAR' , 'NULL', TRUE)->where( ... )->update('MYTABLE');

Actually, try this too while you're at it:

Code:

$this->db->set('ID_PAR = NULL' , '');

Oh, and this should work too:

Code:

$this->db->update('MYTABLE' ,array('ID_PAR' => NULL));

[eluser]comex[/eluser]

$this->db->update('MEDDELANDE' ,array('ID_PAR' => NULL)); does what was desired.
Must be something wrong with my db-handler! Thank you!

Update: had a function that did strip_slashes on the data that was to be submitted, excluding NULL-values did the trick.

[eluser]CroNiX[/eluser]
[quote author="comex" date="1259372327"]
$this->db->update('MEDDELANDE' ,array('ID_PAR' => NULL)); does what was desired.
Must be something wrong with my db-handler! Thank you!

Update: had a function that did strip_slashes on the data that was to be submitted, excluding NULL-values did the trick.[/quote]
Just using the $this->db->update('table_name', $data); will automatically properly escape all values within $data, so no need to use stripslashes.

[eluser]John_Betong[/eluser]
 
Out of curiousity I used phpMyAdmin to try changing an INT field in one of my tables to a default NULL value:

#1067 - Invalid default value for 'pageid'

 
It appears as though NULL is not a valid number.
 
 
WHOOPS: I just clicked the NULL checkbox and it is now working