[eluser]drewbee[/eluser]
Hi All,
I have a table that is of datatype float and it allows NULLS.
I would normally expect if an empty string is passed into the database, it sets it to null. At least this is the behavior with MS SQL. When doing an insert with MySQL, it ends up inserting a '0' (assuming an empty string as well for varchar datatype), even with the DEFAULT VALUE set to NULL.
You have to explicity set null as the value to be inserted
Code:
$insert = array('column' => null);
$this->db->insert('table', $insert);
While I havn't decided exactly where I wanted to put this, I threw it into the form helper even though it should be with the database. Unfortunately, we can't extend the database classes (smoothly)... and I automatically load the form helper anytime the form validation class is loaded so it only seemed logical.
Has anyone else run into this?
Helper function:
Code:
function set_null($value = '', $force_null = FALSE)
{
if ($force_null == TRUE)
{
return NULL;
}
return $value == '' ? NULL : $value;
}
Note: I automatically trim() all POST data. It would be wise to do a trim here as well if you don't.
controller:
Code:
$insert = array('col1' => set_null($this->input->post('field1')),
'col2' => set_null($this->input->post('field2')));
$this->db->insert('table', $insert);
$this->db->update('table', $insert);
Thoughts?