One more important issue about inserting NULL values in the database.
I have a custom field in the Useraccount table ‘user_type’ which is an FK to a lookup table, but it is not required and therefore can be NULL.
Problem is that inserting NULL in the database raises an error, this seems to be related to the ActiveRecord library which escapes all values with backtics which is not accepted for NULL values.
I think this should be handled somewhere in the flexi_auth_model.update_user() function, but I want to share this with you first.
EDIT: An alternative would be, adding a value “not specofied” to the lookup table, thereby elimination the need for a NULL value. But I do not favour this solution, it should be possible to update a nullable field to the value of NULL>
The issue appears to be more general in nature and is described in this thread:
http://stackoverflow.com/questions/3509791/codeigniter-activerecord-update-method-refuses-to-insert-null-value?rq=1
Though I still have no solution, escaping the values can be prevented by using the extra parameter on the $this->db->set method, but when using the field-value array approach, this does not work.
Even Phil Sturgeon commented the issue and consiferes it as a bug, but apparently it has not been solved yet.
I have found a solution by modifying the flexi-auth_model->update_user() method
It holds the assumption that a null value is passed in the post array as the string ‘null’, and does not add the quotes when a ‘null’ value is found. The update query code had to be rewritten for this:
In facyt the same goes for the insert, but in this case the lookup field can just be omitted in which case the database default value us used, which is also null.
(from line 315)
if (count($sql_update) > 0) {
//KVG: handling null values correctly
foreach ($sql_update as $field => $value) {
if (strtolower($value) == 'null') {
$this->db->set($field, $value, FALSE);
} else {
$this->db->set($field, $value);
}
}
$this->db->where($this->auth->tbl_col_user_account['id'], $user_id);
$this->db->update($this->auth->tbl_user_account);
}
Regards, Klaas
