Is there a better way to update as multiple records in Laravel?

Question

I did some research on the subject but at the end I found that my approach was acceptable for updating about a list of 100 records x 10 fields. Is there a better way to do that?

The arrays come from a form looking like this:

 <div class="input-group">                                  
    <div class="col-1">
       <input class="input" type="text" name="id[]" id="id[]" value="...">
    </div>  
    <div class="col-1">
       <input class="input" type="text" name="field1[]" id="field1[]" value="...">
    </div>  
    <div class="col-1">
       <input class="input" type="text" name="field2[]" id="field2[]" value="...">
    </div>  
    <div class="col-1">
       <input class="input" type="text" name="field3[]" id="field3[]" value="..">
    </div>  
    ...
 </div>

In order to save all the changes in the db table, I execute the following PHP in the controller:

 //  update db in bulk | PUT
 public function save($table,$fields='field1,field2,field3...')
 {

    // replace sql statement
    $sqlReplace0 = "UPDATE ".$table." SET XXX_listFields WHERE id = ";

    // fields list into array
    $arrayFields = explode(',',$fields);

    // loop records
    $sql = '';
    $i = 0;
    foreach($_POST['id'] as $id) {

       // loop fields
       $fields = '';
       foreach($arrayFields as $field) {
          if ($fields == '') {
             $fields .= $field." = '".$_POST[$field][$i]."'";
          } else {
             $fields .= ",".$field." = '".$_POST[$field][$i]."'";
          }
       }

       // replace fields & values in sql
       $sql .= str_replace('XXX_listFields',$fields,$sqlReplace0)."'".$id."';\n";

       $i++;

    }

    // update all records
    $result = DB::unprepared($sql); 

    ...

 }

It works pretty fast in my case... but I would like to learn if there is a better way...

Answer 1

To answer you question, you can use the upsert feature of Laravel.

here's an example of upsert:

YourModelName::upsert([
    ['firstfield' => 'request var', 'secondfield' => 'request var', 'thirdfield' => 'request var'],
    ['firstfield' => 'request var', 'secondfield' => 'request var', 'thirdfield' => 'request var']
],  ['firstfield']);

upsert serves as an update or create method, if the data matched with a record in your database (which in this case we use firstfield) than the record will be updated, and if not, then it will be inserted as a new row.

you can also run your variables through a loop, and store them in a collection and then pass it to the upsert method.

here's an example:

$data = [];

for ($i = 0; $i < count($request->id); $i++){
    $data[] = [
        'id'     => $request->input('id')[$i],
        'field1' => $request->input('field1')[$i],
        'field2' => $request->input('field2')[$i],
        'field3' => $request->input('field3')[$i]
    ];
}

YourModelName::upsert($data, ['id']);

hope it helps.