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

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 2

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 3

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.