How can i UPDATE all records using associative array?

Question

I have this table that contains my site settings:

I would want to update all records at once with data that comes from a form. The data looks like this:

$data = [
  "brand" => "bbb"
  "mail" => "kontakt@aaa.pl"
  "phone" => "111"
  "site-name" => "test"
];

Now I would like to update that with key of the associative array and with it value. I tried:

DB::table('settings')->update($data);

But there is an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'brand' in 'field list' (SQL: update `settings` set `brand` = bbb, `mail` = kontakt@aaa.pl, `phone` = 111, `site-name` = test)

Obviously it thinks that brand is a column name. So I transformed the $data to this array:

$data = [
  0 => [
    "name" => "brand"
    "value" => "bbb"
  ]
  1 => [
    "name" => "mail"
    "value" => "kontakt@aaa.pl"
  ]
  2 => [
    "name" => "phone"
    "value" => "111"
  ]
  3 => [
    "name" => "site-name"
    "value" => "test"
  ]
];

and now the error is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update `settings` set `0` = {"name":"brand","value":"bbb"}, `1` = {"name":"mail","value":"kontakt@aaa.pl"}, `2` = {"name":"phone","value":"111"}, `3` = {"name":"site-name","value":"test"})

So now it thinks that index of each row in array is column name and at this place i have no idea how to do this... Can anyone help me please?

I think this question already has an answer here: – freeguy Mar 23 '21 at 21:02 — freeguy, Mar 23 '21 at 21:02
You should probably be clear about your intentions. – blakroku Mar 24 '21 at 01:11 — blakroku, Mar 24 '21 at 01:11

score 0 · Answer 1 · answered Mar 23 '21 at 20:54

I came to this solution although i think it looks ugly and there should be a better way to do this, if no better answers will be given i will mark my answer as correct

$data = [
  "brand" => "bbb"
  "mail" => "kontakt@aaa.pl"
  "phone" => "111"
  "site-name" => "test"
];

foreach($data as $key=>$d) {
      DB::table('settings')->where('name','=',$key)->update(['value' => $d]);
}

score 0 · Answer 2 · answered Mar 24 '21 at 01:38

Your table looks something like you can only take a set of data and update it with time. Why because there are no foreign key relations.

if that is the case then why not have brand_name, mail, phone and site_name for settings columns,

Your migrations

public function up()
{
    Schema::create('settings', function (Blueprint $table) {
        $table->string('brand_name');
        $table->string('mail');
        $table->string('phone');
        $table->string('site_name');
        $table->timestamps();
    });
}

In your model?

protected $fillable = ['brand_name', 'mail', 'phone', 'site_name'];

or

protected $guarded = [];

In your controller

public function method(Request $request)
{
    // You can also abstract this in to a custom request class
    $request->validate([
        'brand_name' => 'required',
        'mail' => 'required',
        'phone' => 'required',
        'site_name' => 'required',
    ]);
    
    // Add data if record doesn't exist, update when it does
    // To use the validated method on request all needed fields must be required
    Settings::updateOrCreate($request->validated())

}

Should in case I was wrong you can still check out Laravel Mass Update https://laravel.com/docs/8.x/eloquent#mass-updates

Laravel Upserts https://laravel.com/docs/8.x/eloquent#upserts

The problem is with time i plan to add more settings, it means that there will be more rows or more columns using your solution. So i have to stick wtih rows. Anyway thanks for your answer — PHPisMyPassion, Mar 24 '21 at 18:03

score 0 · Answer 3 · answered Jul 16 '21 at 18:08

You can use upsert to update multiple records

First transform data to add column name using collection

$data=collect($data)->transform(function ($value,$name){
        return ["name"=>$name,"value"=>$value];
    })->values()->toArray();

and here i used model

Setting::upsert($data,["name"],["value"]);

To understand Upserts

Upsert perform multiple "upserts" in a single query
first argument consists of the values to insert or update
second argument lists the column(s) that uniquely identify records within the associated table.
third and final argument is an array of the columns that should be updated if a matching record already exists in the database.

Also important point

All databases systems except SQL Server require the columns in the second argument provided to the upsert method to have a "primary" or "unique" index.

It means in your mysql table settings column name to be unique index or else it will insert as new row

Ref:https://laravel.com/docs/8.x/eloquent#upserts

How can i UPDATE all records using associative array?

3 Answers3