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

Question

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

Answer 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.

Answer 2

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

Answer 3

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();

  }

Answer 4

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);

Answer 5

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

Answer 6

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);

Answer 7

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.

Answer 8

in my case it solved by using validator in insert() function pass your post name to this

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