Save a nested array in database

when i run the query it is successful but again if i do it from my function i am getting this error

SQLSTATE[01000]: Warning: 1265 Data truncated for column 'id' at row 1 (SQL: insert into `employees` (`id`, `date_of_birth`, `image`, `email`, `first_name`, `last_name`, `title`, `address`, `country`, `bio`, `rating`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 1990-06-29, https://lorempixel.com/640/480/people/?96612, Mr., Dayni, Mayez, Mr., 18342 Alisa Square Suite 259, USA, Maxime ratione optio ratione voluptatem sed rem rerum. Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut quia minima aspernatur. At enim consequuntur sunt blanditiis., 3.0600000000000001, 2020-12-22 19:28:48, 2020-12-22 19:28:48))

this is my modified function:

public function getData()
    {
        $token = "b5846ddac7d27dd4960dcb749ff76642";
        $client = new Client();
        $response = $client->request('GET', 'api_url', [
            'headers' => [
                'Access-Token' => $token,
                'Content-Type' => 'application/json',
            ],
        ]);
        $data = json_decode($response->getBody()->getContents(), true);
        $timestamp = Carbon::now()->toDateTimeString();
        $prepared = collect($data['data'])->map(function($item) use ($timestamp) {
            $item['created_at'] = $timestamp;
            $item['updated_at'] = $timestamp;
            return $item;
        });
        foreach ($prepared as $items) {

            $employee = new Employee();
            $employee->id = $items['id'];
            $employee->date_of_birth = $items['date_of_birth'];
            $employee->image = $items['image'];
            $employee->email = $items['title'];
            $employee->first_name = $items['first_name'];
            $employee->last_name = $items['last_name'];
            $employee->title = $items['title'];
            $employee->address = $items['address'];
            $employee->country = $items['country'];
            $employee->bio = $items['bio'];
            $employee->rating = $items['rating'];
            $employee->save();
        }
   

    }

still im getting the same error....

SQLSTATE[01000]: Warning: 1265 Data truncated for column 'id' at row 1 (SQL: insert into `employees` (`id`, `date_of_birth`, `image`, `email`, `first_name`, `last_name`, `title`, `address`, `country`, `bio`, `rating`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 1990-06-29, https://lorempixel.com/640/480/people/?96612, Mr., Dayni, Mayez, Mr., 18342 Alisa Square Suite 259, USA, Maxime ratione optio ratione voluptatem sed rem rerum. Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut quia minima aspernatur. At enim consequuntur sunt blanditiis., 3.0600000000000001, 2020-12-22 20:04:09, 2020-12-22 20:04:09))

the same array from above

array:5 [▼
  0 => array:11 [▼
    "id" => "8c8c13b6-35ed-3ffb-92d5-c438825df67f"
    "date_of_birth" => "1990-06-29"
    "image" => "https://lorempixel.com/640/480/people/?96612"
    "email" => "[email protected]"
    "first_name" => "Dayni"
    "last_name" => "Mayez"
    "title" => "Mr."
    "address" => "18342 Alisa Square Suite 259"
    "country" => "USA"
    "bio" => """
      Maxime ratione optio ratione voluptatem sed rem rerum.
      \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
      \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
      """
    "rating" => "3.0600000000000001"
  ]
  1 => array:11 [▼
    "id" => "8c2313b6-35ed-3ffb-92d5-c438825df67f"
    "date_of_birth" => "1980-06-29"
    "image" => "https://lorempixel.com/640/480/people/?96613"
    "email" => "[email protected]"
    "first_name" => "Alisa"
    "last_name" => "Milesz"
    "title" => "Mrs."
    "address" => "774 Snider Street"
    "country" => "USA"
    "bio" => """
      Maxime ratione optio ratione voluptatem sed rem rerum.
      \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
      \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
      """
    "rating" => "3.0600000000000002"
  ]
  2 => array:11 [▼
    "id" => "1c2313b6-35ed-3ffb-92d5-c438825df67f"
    "date_of_birth" => "1955-06-29"
    "image" => "https://lorempixel.com/640/480/people/?96614"
    "email" => "[email protected]"
    "first_name" => "Andre"
    "last_name" => "Barbuda"
    "title" => "Mr."
    "address" => "4593 Michigan Avenue"
    "country" => "USA"
    "bio" => """
      Maxime ratione optio ratione voluptatem sed rem rerum.
      \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
      \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
      """
    "rating" => "1.0600000000000001"
  ]
  3 => array:11 [▼
    "id" => "2c2313b6-35ed-3ffb-92d5-c438825df67f"
    "date_of_birth" => "1986-06-29"
    "image" => "https://lorempixel.com/640/480/people/?96616"
    "email" => "[email protected]"
    "first_name" => "James"
    "last_name" => "Stein"
    "title" => "Mr."
    "address" => "Colorado Springs, CO 80903"
    "country" => "USA"
    "bio" => """
      Maxime ratione optio ratione voluptatem sed rem rerum.
      \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
      \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
      """
    "rating" => "5.0600000000000001"
  ]
  4 => array:11 [▼
    "id" => "3c2313b6-35ed-3ffb-92d5-c438825df67f"
    "date_of_birth" => "1982-06-29"
    "image" => "https://lorempixel.com/640/480/people/?96618"
    "email" => "[email protected]"
    "first_name" => "John"
    "last_name" => "Tompkins"
    "title" => "Mr."
    "address" => "4451 Deans Lane"
    "country" => "USA"
    "bio" => """
      Maxime ratione optio ratione voluptatem sed rem rerum.
      \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
      \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
      """
    "rating" => "2.0600000000000001"
  ]
]

in the model should i set every field $fillable?

If you do the proper validation in your controller then you can set

protected $guarded = [];

same errorr......

SQLSTATE[01000]: Warning: 1265 Data truncated for column 'id' at row 1 (SQL: insert into `employees` (`id`, `date_of_birth`, `image`, `email`, `first_name`, `last_name`, `title`, `address`, `country`, `bio`, `rating`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 1990-06-29, https://lorempixel.com/640/480/people/?96612, [email protected], Dayni, Mayez, Mr., 18342 Alisa Square Suite 259, USA, Maxime ratione optio ratione voluptatem sed rem rerum. Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut quia minima aspernatur. At enim consequuntur sunt blanditiis., 3.0600000000000001, 2020-12-22 21:24:30, 2020-12-22 21:24:30))

array:11 [▼
  "id" => "8c8c13b6-35ed-3ffb-92d5-c438825df67f"
  "date_of_birth" => "1990-06-29"
  "image" => "https://lorempixel.com/640/480/people/?96612"
  "email" => "[email protected]"
  "first_name" => "Dayni"
  "last_name" => "Mayez"
  "title" => "Mr."
  "address" => "18342 Alisa Square Suite 259"
  "country" => "USA"
  "bio" => """
    Maxime ratione optio ratione voluptatem sed rem rerum.
    \t\t\t\t  Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut
    \t\t\t\t  quia minima aspernatur. At enim consequuntur sunt blanditiis.
    """
  "rating" => "3.0600000000000001"
]

my Database is completely empty I don't have any data

I want to see the table structure

    public function up()
    {
        Schema::create('Employees', function (Blueprint $table) {
            $table->string('id');
            $table->date('date_of_birth');
            $table->string('image');
            $table->string('email');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('title');
            $table->string('address');
            $table->string('country');
            $table->text('bio');
            $table->bigInteger('rating');
            $table->timestamps();
        });
    }

Not the migration. I want you to log into your database and run desc employees;and show me the output.

sorry about that, here you go : https://prnt.sc/w83saz

yes...still the same....

If you make all the columns nullable except the id and run this

$data = json_decode($response->getBody()->getContents(), true);
$items = $data['data'];
foreach($items as $item) {
            $employee = new Employee();
            $employee->id = $item['id'];
            //$employee->date_of_birth = $item['date_of_birth'];
            //$employee->image = $item['image'];
            //$employee->email = $item['title'];
            //$employee->first_name = $item['first_name'];
            //$employee->last_name = $item['last_name'];
            //$employee->title = $item['title'];
            //$employee->address = $item['address'];
            //$employee->country = $item['country'];
            //$employee->bio = $item['bio'];
            //$employee->rating = $item['rating'];
            $employee->save();

	   dd($item['id']);
}

Does it complain about the id?

Or does it do the insert and then dump?

"8c8c13b6-35ed-3ffb-92d5-c438825df67f"

If it works remove the remark from the next assignment and run again. Repeat until it fails.

strange...when i do this i get

SQLSTATE[HY000]: General error: 1364 Field 'date_of_birth' doesn't have a default value (SQL: insert into `employees` (`id`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 2020-12-22 22:09:21, 2020-12-22 22:09:21))

did you make it nullable in your migration?

yes i did...

Did you run php artisan migrate:fresh?

If you done that and still fail comment out all the fields in your migration except id.

Then run the command again and try to insert.

And if successfull add them back one by one in both places till you fail.

i have refreshed the database and doing this

   foreach($items as $item) {

            $employee = new Employee();
            $employee->id = $item['id'];
//            $employee->date_of_birth = $item['date_of_birth'];
//            $employee->image = $item['image'];
//            $employee->email = $item['title'];
//            $employee->first_name = $item['first_name'];
//            $employee->last_name = $item['last_name'];
//            $employee->title = $item['title'];
//            $employee->address = $item['address'];
//            $employee->country = $item['country'];
//            $employee->bio = $item['bio'];
//            $employee->rating = $item['rating'];

            $employee->save();
            dd($item['id']);

        }

and i am getting

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8' for key 'PRIMARY' (SQL: insert into `employees` (`id`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 2020-12-22 22:29:57, 2020-12-22 22:29:57))

and when i do dd(employee->id) before $employee->save(); i get 8, it is getting only the first character...

Just a random idiot test if you do this in your migration

 public function up()
    {
        Schema::create('Employees', function (Blueprint $table) {
            $table->string('identifier');
            //$table->date('date_of_birth');
            //$table->string('image');
            //$table->string('email');
            //$table->string('first_name');
            //$table->string('last_name');
            //$table->string('title');
            //$table->string('address');
            //$table->string('country');
            //$table->text('bio');
            //$table->bigInteger('rating');
            $table->timestamps();
        });
    }

Then do this in your store method

$data = json_decode($response->getBody()->getContents(), true);
$items = $data['data'];
foreach($items as $item) {
            $employee = new Employee();
            $employee->identifier = $item['id'];
            //$employee->date_of_birth = $item['date_of_birth'];
            //$employee->image = $item['image'];
            //$employee->email = $item['title'];
            //$employee->first_name = $item['first_name'];
            //$employee->last_name = $item['last_name'];
            //$employee->title = $item['title'];
            //$employee->address = $item['address'];
            //$employee->country = $item['country'];
            //$employee->bio = $item['bio'];
            //$employee->rating = $item['rating'];
            $employee->save();
}

do you still get the 8 if you dd the employee?

i have run the same code as you did but i get the same issue

SQLSTATE[01000]: Warning: 1265 Data truncated for column 'id' at row 1 (SQL: insert into `employees` (`id`, `date_of_birth`, `image`, `email`, `first_name`, `last_name`, `title`, `address`, `country`, `bio`, `rating`, `updated_at`, `created_at`) values (8c8c13b6-35ed-3ffb-92d5-c438825df67f, 1990-06-29, https://lorempixel.com/640/480/people/?96612, Mr., Dayni, Mayez, Mr., 18342 Alisa Square Suite 259, USA, Maxime ratione optio ratione voluptatem sed rem rerum.	Eaque voluptatem veniam voluptatum porro. Animi sequi nisi ut	quia minima aspernatur. At enim consequuntur sunt blanditiis., 3.0600000000000001, 2020-12-23 08:50:21, 2020-12-23 08:50:21))

maybe is something to do with my database? i am using mySql Server type: MariaDB. could this be an issue?

The problem was with the database, when i run the code in a new laravel project the data got stored... also i have made a new employee_id field in which i am storing the id that i am getting from the api and have another id( autoincrements ) for every employee.

        foreach($items as $item) {
            $employee = new Employee();
            $employee->employee_id = $item['id'];
            $employee->date_of_birth = $item['date_of_birth'];
            $employee->image = $item['image'];
            $employee->email = $item['title'];
            $employee->first_name = $item['first_name'];
            $employee->last_name = $item['last_name'];
            $employee->title = $item['title'];
            $employee->address = $item['address'];
            $employee->country = $item['country'];
            $employee->bio = $item['bio'];
            $employee->rating = $item['rating'];
            $employee->save();
        }

Thank you very much!

Happy you got it working. :)

Thanks for the reply, but I wend for a different solution where I insert all the data with one insert instead of multiple entries

  $data = json_decode($response->getBody()->getContents(), true);
        $timestamp = Carbon::now()->toDateTimeString();

        $prepared = collect($data['data'])->map(function($item) use ($timestamp) {
            $employee = new Employee();
            $employee->employee_id = $item['id'] ;
            $item['created_at'] = $timestamp;
            $item['updated_at'] = $timestamp;
            return $item;
        });

        //insert in database
        Employee::insert($prepared->toArray());

it works perfect!