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.

Answer 1

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.

Answer 2

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,
    ...
],