Update empty string to NULL in a html form

Question

I'm building a site in Laravel.

I have foreign key constraints set up among InnoDB tables.

My problem is that if i don't select a value in a, say, select box, the framework tries to insert or update a record in a table with '' (empty string). Which causes a MySQL error as it cannot find the equivalent foreign key value in the subtables.

Is there some elegant way to force the insertion of NULL in the foreign key fields other than checking out every single field? Or to force MySQL to accept '' as a "null" foreign key reference?

In other words: I have a, say, SELECT field with first OPTION blank. I leave the blank OPTION chosen. When I submit, an empty string '' is passed. In MySQL apparently I can do UPDATE table SET foreignKey=NULL but not UPDATE table SET foreignKey=''. It does not "convert" to NULL. I could check the fields one by one but and convert '' to NULL for every foreign key, maybe specifying all of them in an array, I was wondering if there's a more streamlined way to do this.

Maybe have to change my ON UPDATE action (which is not set) in my DB schema?

Edit: the columns DO accept the NULL value, the problem is in how the framework or MySQL handle the "empty value" coming from the HTML. I'm not suggesting MySQL "does it wrong", it is also logical, the problem is that you can't set a "NULL" value in HTML, and I would like to know if there's an elegant way to manage this problem in MySQL or Laravel.

In other words, do I have to specify manually the foreign keys and construct my query accordingly or is there another robust and elegant way?

My code so far for the model MyModel:

$obj = new MyModel;
$obj->fill(Input::all())); // can be all() or a subset of the request fields
$obj->save();

Answer 1

At least since v4 of Laravel (and Eloquent models), you can use mutators (aka setters) to check if a value is empty and transform it to null, and that logic is nicely put in the model :

class Anything extends \Eloquent {
    // ...
    public function setFooBarAttribute($value) {
        $this->attributes['foo_bar'] = empty($value)?null:$value;
    }
}

You can check out the doc on mutators.

I've been oriented by this github issue (not exactly related but still).

Answer 2

Instead of using

$obj = new MyModel;
$obj->fill(Input::all())); // can be all() or a subset of the request fields
$obj->save();

Use

$obj = new MyModel;
$obj->fieldName1 = Input::get('formField1');
$obj->fieldName2 = Input::has('formField2') && Input::get('formField2') == 'someValue' ? Input::get('formField2') :  null;
// ...
$obj->save();

And make sure your database field accepts null values. Also, you can set a default value as null from the database/phpmyadmin.