Array to string conversion (SQL: insert into

Question

I'm trying to insert data from a Seeder in Laravel 5.6 and I'm having a problem with the field that is json type. I want this field ('stops') to be an array (for example of ten integers not repeated).

The table seeder (RoutesTableSeeder.php) is something like this:

<?php

 use \Illuminate\Support\Facades\DB;
 use Illuminate\Database\Seeder;
 use Faker\Factory as Faker;

 use App\Models\Route;

 class RoutesTableSeeder extends Seeder
 {
   /**
   * Run the database seeds.
   *
   * @return void
   */
   public function run()
   {
    //factory(Route::class, 20)->create();

    $faker = Faker::create();

    //$values= array();

    /*for($i=0; $i < 10; $i++) {
        $values []= $faker->unique()->randomDigit;
    }

    print_r(json_encode($values));*/

    foreach (range(1, 20) as $index)
    {
        $values = array();

        for($i=0; $i < 10; $i++) {
            $values []= $faker->unique()->randomDigit;
        }

        //print_r($values);

        DB::table('routes')->insert([
            'user_id' => $faker->numberBetween($min = 1, $max = 20),
            'name' => $faker->name,
            'description' => $faker->name,
            'route_photo' => $faker->image($dir = null, $width = 640, $height = 480, $category = null, $fullPath = true, $randomize = true, $word = null),
            'stops'=> [
                        //$values,
                        json_encode($values)
                        //implode(", ", $values)
            ],
        ]);
    }

  }
  }

I tried several ways to insert data. When I use json_encode($values) I have the following error:

Array to string conversion 
(SQL: insert into `routes` (`user_id`, `name`, `description`, `route_photo`, `stops`) 
values (19, Isaac 
  Feil, Holly Nolan, /tmp/bc8a3cf5e015d3afa96317485499e0ca.jpg, 
[8,6,0,7,3,1,5,2,4,9]))

This kind of value [8,6,0,7,3,1,5,2,4,9] is what I want to store in 'stops' field, for example, but I don't know what is going wrong....

Please, would you be so kind to help me? I'm desperate....

I post the model if it helps:

<?php

  namespace App\Models;

  use Illuminate\Database\Eloquent\Model;

  class Route extends Model
    {
      protected $fillable = [
       'user_id',
       'name',
       'description',
       'route_photo',
       'stops'
 ];


   protected $casts = [
    'stops' => 'array'
  ];
 }

And the migration:

  public function up()
{
    Schema::create('routes', function (Blueprint $table) {
        $table->increments('id');
        //FK:users
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
        //FK:users
        $table->string('name');
        $table->string('description')->nullable();
        $table->string('route_photo');
        $table->json('stops');
        $table->timestamps();
    });
 }

Thanks a lot!!

Yaya

Comma delimited lists in a column is a terrible idea – Cavesson

You can't use an array as a string – Messiaen

You can use seralize and then on retrieve use unseralize to get back an array – Neri

Answer 1

json_encode($values) returns a string, which you can use as the value of the stops column. There's no need to put [] around it, that creates an array, and you can't store an array directly into a column. Just leave out the brackets:

'stops' => json_encode($values)

However, storing arrays in database columns is generally a bad idea, it violates normalization principles. You should use a separate table with a row for each value.

Denbighshire

thank you so much!!...and anything else, could you know how to create $ values with no repeated values? Iḿ having problems using unique() in faker – Yaya

I'm not very familiar with Faker, but there's an example of unique() in the documentation: github.com/fzaninotto/Faker – Denbighshire

@Barmar, thanks for this it works. One question - the values and stored like this ["elOne", "elTwo"]. How do I store this without the [""]? – Ratan

If you leave out the quotes, it won't be valid JSON, so you won't be able to decode it when you retrieve it. Why do you want to do that? – Denbighshire

If you just want to store a comma-separated list, use implode() instead of json_encode. – Denbighshire

But you said in the question that the datatype of the column is JSON. That requires it to be in valid JSON format. – Denbighshire

Answer 2

Don't cast stops to array, First remove

protected $casts = [
    'stops' => 'array'
];

And use json_encode to make string

'stops'=> json_encode($values),

Chevalier

thank you so much!!...and anything else, could you know how to create $ values with no repeated values? Iḿ having problems using unique() in faker – Yaya

create a function to make random number, check with in_array if found re generate if not store in a array. that a logic. – Chevalier