1

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

Paul Godard
  • 1,055
  • 1
  • 14
  • 30
  • 1
    **Warning!** You're wide open for [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) by using prepared statements with bound parameters instead of injecting variables directly into your queries. It's not just about security. If your data contains, for example, a single quote `'`, your query will break. – M. Eriksson May 28 '23 at 12:22
  • Laravel automatically replaces the question marks with the query variable, automatically escaping the input variables. This protects the code from SQL injection attacks. Please ignore the statement above about being open to sql attacks. – Gerald Thornton May 28 '23 at 14:20
  • Laravel protects against sql attacks automatically, you are good... – Gerald Thornton May 28 '23 at 14:29

1 Answers1

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.

Yusuf Giovanno
  • 131
  • 10