+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Database INSERT changes NULL value to empty string (/showthread.php?tid=64170)
Database INSERT changes NULL value to empty string - webmachine - 01-22-2016
I have a field in one of my database tables, careplanner_id, smallint(5), that is set as a foreign key. It allows NULL values, and its DEFAULT is set to NULL.
When I try to insert a record into the table with an empty form field for careplanner_id, CodeIgniter set its value in its mySQL query to an empty string instead of NULL, and then my foreign key designation fails.
Is this something CodeIgniter does, or should I be looking for a solution somewhere else, or should I just give up with the foreign key designation and deal with changes manually?
RE: Database INSERT changes NULL value to empty string - Narf - 01-22-2016
An empty form field == an empty string. If that's what you pass to CI, that's what it will insert.
RE: Database INSERT changes NULL value to empty string - webmachine - 01-22-2016
Thanks. So for a FK to an id integer value, if the form select for careplanner_id is to be not selected, which would be best to use - an empty string, NULL or 0? I need to go back and change my CodeIgniter Form Helper option value for this case.
RE: Database INSERT changes NULL value to empty string - meow - 02-29-2016
(01-22-2016, 11:10 AM)Narf Wrote: An empty form field == an empty string. If that's what you pass to CI, that's what it will insert.
Thank you. Are you sharp or what? By the way, only found this thread through a Bing search. The forum search function was unable to find for me.
RE: Database INSERT changes NULL value to empty string - InsiteFX - 02-29-2016
Try setting your default value to 0 ( zero ).
RE: Database INSERT changes NULL value to empty string - meow - 03-04-2016
Careful with zero. If I'm logging temperatures for example in my webapp, then a zero implies that I recorded zero as opposed to leaving it NULL or blank. Another example: zero inches of snow were recorded or NULL inches were recorded? Difference.
RE: Database INSERT changes NULL value to empty string - webmachine - 03-04-2016
Not a problem here. The issue is with foreign key id's that don't have to be entered. I'm finding that even if I set the default to NULL, a 0 is entered, so I decided to stick with 0.
RE: Database INSERT changes NULL value to empty string - Narf - 03-04-2016
(03-04-2016, 01:07 PM)webmachine Wrote: Not a problem here. The issue is with foreign key id's that don't have to be entered. I'm finding that even if I set the default to NULL, a 0 is entered, so I decided to stick with 0.
Now that you say this, I think there's another side to it - unless I'm mistaken, you cannot have a foreign key reference to a NULL "value".
I'm quoting that word on purpose, because NULL is in fact not a value, but a token for lack of value and therefore a foreign key doesn't make sense - a reference can't point to nothing. This is not limited to databases, but there are a lot of other instances in DB programming where it becomes obvious, like COUNT(field_name) ignoring rows where field_name is NULL.