CODEWITHSUNDEEP
phpmysqllaravel
Metono
Metono

Reputation: 87

How to update multiple rows in sql from array data using laravel eloquent

I was trying to update multiple records in my database using laravel eloquent but is getting errors when trying to update using an array.

I am not really sure how to correctly get the data from the array to my update function.

The array I am passing looks like this.

enter image description here

My Database table looks like

id | checklistid | categoryid | isCheck | created_at | updated_at

My Controller looks like this.

public function updateCategoryListData(Request $request){
    $checklistdata = $request->get('checklist');
    $id = $request->get('checklistid');
    $dataset = [] ;
    foreach($checklistdata as $key =>$value){
                $dataset[] = ['checklistid'=>$id,'categoryid' => $key,'isCheck'=>$value];
           }
        categorylistcontent::where([['checklistid',$id], ['categoryid', $dataset=>['categoryid'] ]])
            ->update($dataset['isCheck']);
}

Would you be able to advise how I can use the array to get the 'checklistid' and 'categoryid' to be used as the where clause of the update statement and then the 'isCheck' to be set in the update.

Upvotes: 4

Views: 4565

Answers (3)

Pooja Yengandul
Pooja Yengandul

Reputation: 41

You need to update multiple rows by putting it in foreach loop

eg:

foreach($checklistdata as $key =>$value){
$dataset[] = ['checklistid'=>$id,'categoryid' => $key,'isCheck'=>$value];

categorylistcontent::where([['checklistid',$id], ['categoryid', $dataset=>['categoryid'] ]])
            ->update($dataset['isCheck']);
}

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

You can't do that with just one query, but you could do that with two queries. An example:

$check = categorylistcontent::query();
$notCheck = categorylistcontent::query();

foreach ($request->checklist as $item) {
    $query = $item['isCheck'] === 1 ? 'check' : 'notCheck';
    $$query->orWhere(function($q) use($item) {
        $q->where('checklistid', $item['checklistid'])->where('categoryid', $item['categoryid']);
    }
}

$check->update(['check' => 1]);
$notCheck->update(['check' => 1]);

I haven't tested this exact code, but I think it will be helpful for you to get the idea.

Upvotes: 1

Sohel0415
Sohel0415

Reputation: 9853

You don't need dataset array, rather do the following:

foreach($checklistdata as $key =>$value){
    categorylistcontent::where('checklistid',$id)->where('categoryid',$key)
        ->update(['isCheck'=>$value]);
}

Upvotes: 1

Related Questions