Framework: Eloquent assigns 0000-00-00 to nullable date fields

[vstokesjr]

Laravel 4: (updated today)
Database: MySQL 5.5.32 (Ubuntu)
PHP: 5.4.9-4ubuntu2.2

In my migration I have:
....
$table -> date( 'visible_to' ) -> nullable();
....

And in my controller, when I have set the parameter to NULL, Eloquent still fills the date field with '0000-00-00'.
....
$mNews -> visible_to = null;
$mNews -> save();
....

[stormpat]

What engine are you on? and whats the MySQL mode at?

[vstokesjr]

Engine? Do you mean:
config.database.fetch => PDO::FETCH_CLASS
config.database.connections.[name].driver => mysql

The MySQL mode is default for 5.5. We don't set the mode in the my.cnf.

[stormpat]

By engine i mean like InnoDB or MyISAM..

The default mode is set to none (i think), you can check the mode like this:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

If you want strict errors and no warnings change it to TRADITIONAL

[vstokesjr]

Ok, Yes, Engine is InnoDB.
And those two mode commands return nothing. So I'm guessing it's NONE.

[stormpat]

Try with this,

SET sql_mode = TRADITIONAL;

[vstokesjr]

No good. Same result. Also noticed, that even with sql_mode = 'TRADITIONAL', that VARCHAR's are also not stored as NULL although the VARCHAR field is null-able.

Tested with this app/config/database connection configuration:

    'mysql_intranet' => array(
        'driver' => 'mysql',
        'host' => 'server',
        'database' => 'db',
        'username' => 'user',
        'password' => 'password,
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode = \'TRADITIONAL\''
        ),
    ),

[stormpat]

Laravel sets all columns to NOT NULL by default, if you want to use NULL you haveto add ->nullable();

You can check your table that the value is correct

mysql> describe locations;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| location | varchar(255)     | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

And if you try to insert a NULLto that table you will get an error.

DB::table('locations')->insert(array('location' => NULL));

And the obvious error message:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'location' cannot be null (SQL: insert into `locations` (`location`) values (?)) (Bindings: array ( 0 => NULL, ))

Double check your SQL_MODE and you should be fine! You should also validate your input in your models, and even on the client side.

[stormpat]

EDIT: I think I misunderstood :D Sometimes the pirate patches on my eyes after 12h of coding gets to you! Well, anyway what you probably meant was why you cannot add NULL values to a tables that accepts NULL values.

It should not be a problem, and i still think you have your MySQL config somewhat wrong.

Create the table.

Schema::create('testings', function($table){
            $table->increments('id');
            $table->string('name');
            $table->date('pickup')->nullable();
            $table->timestamps();
        });

Add to the table.

    $test = new Testing;
    $test->name = 'John Doe';
    $test->pickup = new DateTime;
    $test->save();

Result:

mysql> select * from testings;
+----+----------+------------+---------------------+---------------------+
| id | name     | pickup     | created_at          | updated_at          |
+----+----------+------------+---------------------+---------------------+
|  1 | John Doe | 2013-08-29 | 2013-08-29 09:52:28 | 2013-08-29 09:52:28 |
+----+----------+------------+---------------------+---------------------+
1 row in set (0.04 sec)

And with a NULL value.

    $test = new Testing;
    $test->name = 'John Doe';
    $test->pickup = NULL;
    $test->save();

Result.

mysql> select * from testings;
+----+----------+------------+---------------------+---------------------+
| id | name     | pickup     | created_at          | updated_at          |
+----+----------+------------+---------------------+---------------------+
|  1 | John Doe | 2013-08-29 | 2013-08-29 09:52:28 | 2013-08-29 09:52:28 |
|  2 | John Doe | NULL       | 2013-08-29 09:53:13 | 2013-08-29 09:53:13 |
+----+----------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

And when you remove the nullable();you get the error:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'pickup' cannot be null (SQL: insert into `testings` (`name`, `pickup`, `updated_at`, `created_at`) values (?, ?, ?, ?)) (Bindings: array ( 0 => 'John Doe', 1 => NULL, 2 => '2013-08-29 09:56:26', 3 => '2013-08-29 09:56:26', )) 

So the problem is most likely in your database settings.

Sorry for the mixup :/

[vstokesjr]

I found the issue - actually you did:

"You should also validate your input in your model..."

So, I stepped through the code and found that in my controller:

...
$mInput[ 'myvar' ] = Input::get( 'myvar', null);
...

was not behaving as I expected. The browser is sending an empty string back for INPUT TEXT fields. I thought browsers sent nothing if the field was empty. Therefore I was seeing this in my debug evaluation:

$mInput[ 'myvar' ] = '' (string)

and of coarse, in PHP, this statement evaluates as TRUE (note double equals):

if ( $mInput[ 'myvar' ] == null ) {...}

while this is statement does not (note triple equals):

if ( $mInput[ 'myvar' ] === null ) {...}

Therefore, this logic was causing the problem:

...
$mInput[ 'myvar' ] = Input::get( 'myvar', null);
...
$mModel -> myvar = $mInput[ 'myvar' ];
$mModel -> save();
...

My solution was to do this:

...
$mInput[ 'myvar' ] = ( Input::get( 'myvar' ) == '' ? null : Input::get( 'myvar' ) );
...
$mModel -> myvar = $mInput[ 'myvar' ];
$mModel -> save();
...

My apologies for the false report.

[JoostK]

Tip, you could use the shorthand:

$mInput['myvar'] = Input::get('myvar') ?: null;

If Input::get('myvar') is falsy, it will fallback to null.