SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Earphone' for column inventory.products.category_id at row 1 .

My product name is string type and it's join with the categories table. But the category id is integer type.

Query Code:

public function index()
{
    //
    $products = DB::table('products')
                ->join('categories','products.category_id','categories.id')
                ->join('suppliers','products.supplier_id','suppliers.id')
                ->select('categories.category_name','suppliers.name','products.*')
                ->orderBy('products.id','DESC')->get();
    ;
    return response()->json($products);
}

This is my products table

Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->integer('category_id');
            $table->string('product_name');
            $table->string('product_code');
            $table->string('root')->nullable();
            $table->string('buying_price')->nullable();
            $table->string('selling_price');
            $table->integer('supplier_id');
            $table->string('buying_date');
            $table->string('image')->nullable();
            $table->string('product_quantity');
            $table->timestamps();
        });

This is category table :

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('category_name');
        $table->timestamps();  
    });
}

Please fix me where am I doing wrong?

CodePudding user response：

    Schema::create('products', function (Blueprint $table) {
                $table->id();
                $table->bigInteger('category_id')->unsigned();
                $table->string('product_name');
                $table->string('product_code');
                $table->string('root')->nullable();
                $table->string('buying_price')->nullable();
                $table->string('selling_price');
                $table->integer('supplier_id');
                $table->string('buying_date');
                $table->string('image')->nullable();
                $table->string('product_quantity');
                $table->timestamps();
            });
    
    $table->foreign("category_id")->references("id")->on("categories");


////////////////// end migration ////////////////////////////
    
    $products = DB::table('products')
                    ->join('categories','products.category_id','=','categories.id')
                    ->join('suppliers','products.supplier_id','=','suppliers.id')
                    ->select('categories.category_name','suppliers.name','products.*')
                    ->orderBy('products.id','=','DESC')->get();

You should edit code like that. After i hope it is work.

CodePudding user response：

Make id column autoincrement in your migrations as bellow

$table->bigIncrements('id');