How to update null in mySQL using Laravel

Question

I am trying on an api of insert and update, and has problems on null. Already searched almost all questions and solutions but still got the problem, please help me.

table DDL:

CREATE TABLE `brand` (
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_name_en` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `brand_name_ch` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `order_no` int(5) DEFAULT NULL,
  PRIMARY KEY (`brand_id`),
  KEY `index_brand` (`brand_name_en`)
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Code:

public function editBrand($data){
    try{
        $en = $data['brand_name_en'];
        $ch = $data['brand_name_ch'];
        $order = $data['order_no'] ? $data['order_no'] : null;
        $brand_id = $data['brand_id'];
        $this->db = DB::table('brand')
        ->where('brand_id', $brand_id)
        ->update(["brand_name_en" => $en, "brand_name_ch" => $ch, "order_no" => $order]);
        return $this->db;
    }catch(\Exception $e) {
        $this->log('BrandRepo-editBrand Error');
        $this->log($e->getMessage());
        return 'SQL ERROR';
    }
}

and got this Error below:

General error: 1366 Incorrect integer value: 'null' for column 'order_no' at row 1 (SQL: update `brand` set `brand_name_en` = TEST, `brand_name_ch` = 測試, `order_no` = null where `brand_id` = 122)

Guys I just found out the problem is in this line:

$order = $data['order_no'] ? $data['order_no'] : null;

I guess what pass in is string, and its controller has:

$data['order_no'] = $request->order_no ? $request->order_no : null;

Somehow I still know why but I change it into:

$data['order_no'] = $request->order_no ? $request->order_no : 'null';

and back to editBrand() :

$order = ($data['order_no'] != 'null') ? $data['order_no'] : null;

It works now.

Your schema says `order_no` int(5) DEFAULT NULL , which does not correspond with the error you have posted. — Qirel
– Qirel, Commented Jul 9, 2019 at 5:01

xenon · Accepted Answer · 2019-07-09 04:36:54Z

2

This error is because to interger value. integer cannot store null value if not set as null yes in database meaning if not set null mysql will check that need to be insert value but null value is not a interger type.

answered Jul 9, 2019 at 4:36

xenon

904 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

matticustard · Accepted Answer · 2019-07-09 04:57:14Z

1

As xenon states, the default expectation is that you will either define integer fields as nullable within your database, or provide a valid numeric value upon saving.

However, it is possible to override this behavior if you disable 'strict' mode in your database configuration file.

/config/database.php

'mysql' => [
    ...
    'strict' => false,
    ...
],

answered Jul 9, 2019 at 4:57

matticustard

5,1691 gold badge17 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to update null in mySQL using Laravel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related