Make Laravel use MySQL default value on insert/update

Question

I've noticed that, in Laravel, (when using $model->fill(Input::all()), not that it matters how the data comes in), empty fields (empty in a form) come through as an empty string (''). That makes sense, as that's how it's delivered from browser to HTTP server to PHP.

The problem is that if the column is numeric and has a DEFAULT value, or is NULLable, the generated query from Eloquent has '' for the column's value and so MySQL interprets that and enters the value as 0 rather than the default column value or NULL. Is it something I'm doing wrong here, or will I need to put extra work in (e.g. a mutator) to detect this empty string and convert to null to achieve what I actually want?

Of course I understand that from a technical point of view, Laravel, without knowing how your columns work, can't just assume that empty string means pass NULL to the INSERT query, because sometimes you actually want to set a field (specifically a character-based one) to an empty string rather than NULL.

That said, I'd rather not have to define mutators for all my models just because I'm using $model->fill(), but is there anything I don't know about that I can do?

---

For the MySQL people reading this - is it correct behaviour to set a numeric field to 0 if passed ''? Seems like it should be seen as NULL as it's not explicitly 0, but I guess it's maybe weak typing equating '' to 0 rather than the more distant NULL.

Answer 1

There is a very simple way to do this, and that is by using an array_filter.

$input = array_filter(Input::all(), 'strlen');
$model->fill($input);

The array_filter will return all of the keys that have something assigned to them.

There are some caveats with this solution:

• strlen has been used, and not empty. This is because empty will cause other items (such as the number 0) to also be unset.
• this means that edits that are made with an update, such as a text box being completely emptied, will not be fulfilled by your application, so use wisely!

---

EDIT: As for the MySQL question, yes, this is normal.

mysql> SELECT CAST("tim?" AS SIGNED);
+------------------------+
| CAST("tim?" AS SIGNED) |
+------------------------+
|                      0 |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'tim?' |
+---------+------+-------------------------------------------+