Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Array to string conversion (SQL: insert into

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!!

like image 888
Ber Tsacianegu del Tepuy Avatar asked Apr 18 '18 16:04

Ber Tsacianegu del Tepuy


People also ask

Can we insert array in SQL?

How to insert Array elements in SQL? We can insert array elements in an array by mentioning them within curly braces {} with each element separated by commas. Here is an example to illustrate the method for element addition in an array in SQL.

What is the syntax of insert into?

INSERT INTO Syntax Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...)

Does insert into append SQL?

You can also use INSERT INTO to append a set of records from another table or query by using the SELECT … FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table.

How does insert into select work?

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.


2 Answers

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.

like image 182
Barmar Avatar answered Oct 03 '22 23:10

Barmar


Don't cast stops to array, First remove

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

And use json_encode to make string

'stops'=> json_encode($values),
like image 27
Niklesh Raut Avatar answered Oct 03 '22 23:10

Niklesh Raut