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?' |
+---------+------+-------------------------------------------+

Answer 2

For the default values to be inserted on some fields then do not set values for those fields explicitly.

When you receive form data, process each of the fields and decide for which of them you want to insert the default values. Then remove them from insert into ... statement.

Example:
Form fields: field1_1, field_2.

If valid input is given for both of them, then you can write sql statement like:

insert into my_table values( field_1_value, field_2_value );

If one of the fields, say field_2 does not have valid input and you still want the record go into the database table. Then write the sql statement like:

insert into my_table( field_1 ) values( field_1_value );

When you do this, SQL engine will use the default valued defined on the omitted fields in the insert statement.

Note: This only works when you have set default someValue on the columns at the time of creating or altering them in a database table.