How to update mysql table with array of data

I am trying to update multiple rows using this code:

$data = [];
foreach ($projectFieldOptions as $mapping) {
    $data[] = [
        'import_field_slug' => $mapping['value'],
        'internal_field_slug' => $mapping['text'],
        'custom_field'  => $mapping['custom'],
        'import'        => 1,
        'date_upd'      => $now,
    ];
}

$update_feed_mappings = DB::table($db_name . '.feed_mappings')
->where('id_project', $token)
->where('id_feed', $id_feed)
->update($data);

But I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update `shibbir_DB1`.`feed_mappings` set `0` = {\"import_field_slug\":\"date\",..........

Would you tell me how can I fix it?

CodePudding user response：

update only works for a single row and each key must correspond to a column in your sql table. You can't use if with a multi-dimensional array.

You should move your update inside your loop to fix the issue.

foreach ($projectFieldOptions as $mapping) {
    $data = [
        'import_field_slug' => $mapping['value'],
        'internal_field_slug' => $mapping['text'],
        'custom_field'  => $mapping['custom'],
        'import'        => 1,
       'date_upd'      => $now,
    ];

    DB::table($db_name . '.feed_mappings')
        ->where('id_project', $token)
        ->where('id_feed', $id_feed)
        ->update($data);
}

CodePudding user response：

Consider the code below please

$data = [];
$projectFieldOptions = 

$data[] = [
  'import_field_slug' => 1,
  'internal_field_slug' => 1,
  'custom_field'  => 1,
  'import'        => 1,
   'date_upd'      => 1,
];
    
$data[] = [
  'import_field_slug' => 2,
  'internal_field_slug' => 2,
  'custom_field'  => 2,
  'import'        => 2,
  'date_upd'      => 2,
];
    
var_dump($data);

The output will be

array(2) {
  [0]=>
  array(5) {
    ["import_field_slug"]=>
    int(1)
    ["internal_field_slug"]=>
    int(1)
    ["custom_field"]=>
    int(1)
    ["import"]=>
    int(1)
    ["date_upd"]=>
    int(1)
  }
  [1]=>
  array(5) {
    ["import_field_slug"]=>
    int(2)
    ["internal_field_slug"]=>
    int(2)
    ["custom_field"]=>
    int(2)
    ["import"]=>
    int(2)
    ["date_upd"]=>
    int(2)
  }
}

So your update translates into something like that

update(['0' => array(...))

But update method needs something like that

update(['import_field_slug' => 1,...])

Conclusion: please update each record one by one inside your loop