Update multiple rows of database in Laravel

Question

I want a code for update multiple rows of database, somthing like this:

UPDATE values SET data='{"options":["male","female"],"default":"male"}' where project_id=1 and id=1;
UPDATE values SET data='{"options":["male","female"],"default":"male"}' where project_id=1 and id=2;
UPDATE values SET data='{"options":["male","female"],"default":"male"}' where project_id=1 and id=3;

After some hours i could get result with something like this in laravel framework:

$values = Value::where('project_id', $id)->get();
$sql = "";
foreach($request->fields as $field) {
  if(!empty($field->default)) { //New default value is set
    foreach($values as $value) {
      $data = json_decode($value->data, true); /**data column as json object in mysql database **/
      $data["default"] = $field->default;
      $data = json_encode($data);
      $sql .= "update ".DB::connection()->getDatabaseName().".values set data='".$data."' where id="."$value->id;";
    }
  }
}
DB::unprepared($sql);

but this code is not a good practice! So my question is

Is there any ORM way to do this better?!

Vishal Tarkar · Accepted Answer · 2020-02-11 04:54:18Z

25

Here is an easy way to do it.

$values = Value::where('project_id', $id)->update(['data'=>$data]);

I found it from this link

Hope it helps.

edited Feb 11, 2020 at 4:54

answered Apr 5, 2016 at 10:46

Vishal Tarkar

83612 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

Assuming all records should have the same values, this is the correct answer. You could change the link to the manual instead of another site.

vitalik_74 · Accepted Answer · 2015-02-16 11:45:46Z

-6

    $values = Value::where('project_id', $id)->get();
    $sql = "";
    foreach($request->fields as $field) {
      if(!empty($field->default)) { //New default value is set
        foreach($values as $value) {      
          $tmp=$value->data;
          $tmp->default = $field->default;
          $value->data = $tmp;
          $value->save();
        }
      }
    }

And in Value model use Mutator and Accessor, like this

public function getDataAttribute($value)
{
    return json_decode($value);
}
public function setDataAttribute($value)
{
    $this->attributes['data'] = json_encode($value);
}

See documentation http://laravel.com/docs/4.2/eloquent#accessors-and-mutators

Work - https://yadi.sk/i/BnC3HYozehoy2

edited Feb 16, 2015 at 11:45

answered Feb 16, 2015 at 10:47

vitalik_74

4,6112 gold badges21 silver badges27 bronze badges

8 Comments

Hamid Zamani Over a year ago

Thank you very much @vitalik_74 but there is an error: Indirect modification of overloaded property Value::$data has no effect

vitalik_74 Over a year ago

Sorry, is wrong $this->attributes['dmm_month']. I do copy\paste from my project :) . I change in answer to $this->attributes['data']

vitalik_74 Over a year ago

I run this code. And change. Please, see my answer and screenshort.

Hamid Zamani Over a year ago

But still same error for me! Is there any way to save all changes once after foreach? I think this way is slow when database records are much

vitalik_74 Over a year ago

Ok. My code and your not equal. Use $tmp=$value->data; $tmp->default = $field->default; $value->data = $tmp; $value->save();

|

Collectives™ on Stack Overflow

Update multiple rows of database in Laravel

2 Answers 2

1 Comment

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related