Hey! I found the post below while searching for a solution to "prepForDB null values".
[quote author="jTaby" date="1212624776"]gusa, Actually, I'm not sure you can do that without modifying the code a little bit. At first glance, my instinct was to tell you to create a new plugin exactly like TextBox, but with a different prepForDb function. But if you return NULL from prepForDb, then the whole thing will be omitted from the query. If that's fine, then go ahead and do it. If you intend on setting the value as NULL, then I would add a checkbox and in your plugin's prepForDb function, I would do what needs to be done and return NULL...[/quote]
The ability to set a field to NULL when it's empty is very important. INSERTing new records is no problem, because setting the value to NULL omits it from the SQL INSERT and the default NULL in the database can take care of that.
The big problem arises when you want to UPDATE a value and set it to null. The source of the issue is in this function in plugins/codexevents.php
Code:
function prepForDb($data=array()){
$result = $this->CI->codexforms->iterate('prepForDb',$data);
foreach($result as $field=>$v)
if($v === NULL){
unset($result[$field]); // PROBLEM!
}
else if(is_array($v)){
foreach($v as $key=>$val){
if($val !== NULL) // ALSO, A PROBLEM.
$result[$key] = $val;
}
array_merge($result,$v);
unset($result[$field]);
}
return $result;
}
I'm going to guess that the motivation for unsetting the field was to add the ability to remove a field completely from the INSERTs/UPDATEs??? However, null has important meaning and another mechanism is necessary. (CodeIgniter knows how to handle null values.)
Personally, I will be changing the above code to this:
Code:
function prepForDb($data=array()){
$result = $this->CI->codexforms->iterate('prepForDb',$data);
foreach($result as $field=>$v)
if(is_array($v)){
foreach($v as $key=>$val){
$result[$key] = $val;
}
array_merge($result,$v);
unset($result[$field]);
}
return $result;
}
If I encounter problems with this change, I'll post them here. jTaby, if you foresee problems already, please let me know. Thanks!
UPDATE (20080922)
Ok, the above worked alright until I tried to use the ManyToMany plugin, which returns NULL from the prepForDB function. The expected result being that the value would not be inserted/updated since it would be removed. With the change I made above, the ManyToMany field was added to the UPDATE/INSERT statement as field = NULL, but the field doesn't exist in that table, so it fails.
Here's my suggested change; I'll go with it until I find another problem. prepForDB functions can now return a "NULL" string which will be converted to a null value to be inserted/updated. If prepForDB returns NULL (not a string, but the literal), it won't be included in the statement. The only downside is that you can't insert a NULL string into a text field in the database, but hopefully that doesn't come up very often.