'SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' in Laravel

Laravel Multiple Data Insert Error

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'unit_id' at row 2 (SQL: insert into product_prices (created_at, product_id, unit_id, updated_at) values (2016-12-06 06:56:01, 27, 1,2016-12-06 06:56:01), (2016-12-06 06:56:01,27, , 2016-12-06 06:56:01))

But my unit_id field in nullable(); Please someone help me Here column_name=unit_id

phpmysqllaravellaravel-5pivot-table


Solution 1:[1]

null is different than not existend. If you want to set null as a value you have to write it in your query:

 ... ('2016-12-06 06:56:01',27, null, '2016-12-06 06:56:01'))

Also the datetime format is wrong. You have to enter it as a string.

Solution 2:[2]

You are using unit_id is it referenced with units(id) ?. You are inputing a empty value for a foregin key referenced column.use null and not ''

 insert into product_prices (created_at, product_id, unit_id, updated_at)
  values (2016-12-06 06:56:01, 27, 1,2016-12-06 06:56:01), 
         (2016-12-06 06:56:01,27,null, 2016-12-06 06:56:01);

Solution 3:[3]

Set 0 for unit_id before query, if it null/empty. See example:

if(!isset($unit_id) || empty($unit_id)) $unit_id = 0;
.
.
//insert query rest code

Solution 4:[4]

Just had the same issue and in my case it was a silly mistake in my controller.

What I did was I returned the whole object instead of just the id, like so: 

  public function store($id, Request $request) {

    $post = Post::find($id);

    $comment = new Comment;
    $comment->text = $request->comment;
    $comment->post_id = $post; <--- HERE IS THE MISTAKE 
    $comment->post_id = $post->id; <--- HERE IS THE FIX
    $comment->user_id = Auth::id();

    $comment->save();

    return back();

  }

Solution 5:[5]

Using the intval() function can solve the problem quite well.

intval($unit_id);

This code will return 0 if:

  • $unit_id is not set.
  • $unit_id is string and not alphanumeric (e.g. 'abc').
  • $unit_id is NULL

If $unit_id is alphanumeric string (e.g '10'), it'll return the number.

Solution 6:[6]

I was encountering the same error. update_at and created_at are automatically created by migration. Backup your model and delete it. Then create a new Model using php artisan use php artisan make:model Model Name -m then in your migration table add the fields you need eg.

 public function up()
{
    Schema::create('flights', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('airline');
        $table->timestamps();
    });
}

then do the migration php artisan migrate

Solution 7:[7]

i can solve it, check your payload when sent request put or post to your api

i should send this json:

{
...,
"item":1
}

but i sent this. this is my mistake

{
...,
"item":[{"id"=1}]
}

or { ..., "item":[1] }

my code in laravel is this:

   $item= Item::find($request->input('item'));
   $client->item()->associate($item);

Solution 8:[8]

I solved it by using a validator function like so:

function if_set($v)
    if (isset($_POST[$v]) and $_POST[$v] != '')
        $data_to_db[$v] = $_POST[$v];

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Parithiban
Solution 3 AddWeb Solution Pvt Ltd
Solution 4 Kaloyan Drenski
Solution 5 Luciano
Solution 6 jibril90
Solution 7 saber tabatabaee yazdi
Solution 8 oguz ismail

Related Questions