Laravel一次更新多条记录，批量更新的方法
在我们实际应用中，免不了这样的情况——例如我们同时录入多条信息，可能三条五条还好说，但量一旦变大，就会增加读写数据库的次数，会降低效率，那么，我们该如何实现，做到一次读写数据库，批量更新呢？
例如这种情况：
HTML代码：
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<form action="/enroll/complete_order_store" method="post">
{!!csrf_field()!!}
@foreach($seats as $s) //多个座位信息，每个都有他的ID
<br>
<br>
<br>
座位号：{{$s->id}}({{$s->rows."排".$s->cols."座"}})
<input type="hidden" name="id{{$s->id}}" value="{{$s->id}}">
<br>
姓名:<input type="text" name="name{{$s->id}}" id="" value="{{$s->name}}"> //为了区别不同ID提交的不同name，所以在name后面加上了ID
<br>
手机:<input type="text" name="mobile{{$s->id}}" id="" value="{{$s->mobile}}">
<br>
学校/单位:<input type="text" name="school{{$s->id}}" id="" value="{{$s->school}}">
<br>
电子邮箱:<input type="text" name="email{{$s->id}}" id="" value="{{$s->email}}">
<br>
是否需要发票: 是<input type="radio" name="is_bill{{$s->id}}" value="1" {{$s->is_bill == 1?"checked":""}}>否<input type="radio" name="is_bill{{$s->id}}" value="0" {{$s->is_bill == 0?"checked":""}}>
<br>
地址:<input type="text" name="address{{$s->id}}" id="" value="{{$s->address}}">
<br>
邮编:<input type="text" name="post_code{{$s->id}}" id="" value="{{$s->post_code}}">
@endforeach
<input type="hidden" name="ids" value="{{$ids}}"> //这里ids是把所有id拼接成带逗号的字符串，例如 1,2,3
<input type="submit" value="OK">
</form>
</body>
</html>
//test data
/*
$multipleData = array(
array(
"id" => "My id" ,
"name" => "My Name 2" ,
"date" => "My date 2"
),
array(
"id" => "Another id" ,
"name" => "Another Name 2" ,
"date" => "Another date 2"
)
)
*/ public function completeOrderStore(Request $request){
$ids = Input::get("ids"); //拿到逗号分隔的字符串，然后把他们变成数组
$arr = explode(",", $ids);
$multipleData = [];
foreach($arr as $a){ //组成例子那样的数组
$multipleData[] = ["id"=>$a,
"name"=>Input::get("name".$a),
"mobile"=>Input::get("mobile".$a),
"school"=>Input::get("school".$a),
"email"=>Input::get("email".$a),
"is_bill"=>Input::get("is_bill".$a),
"address"=>Input::get("address".$a),
"post_code"=>Input::get("post_code".$a)
];
}
$res = $this->updateBatch("seats",$multipleData);
return Redirect::to("/enroll/myorder");
}//同时更新多个记录，参数，表名，数组（别忘了在一开始use DB;）
public function updateBatch($tableName = "", $multipleData = array()){
if( $tableName && !empty($multipleData) ) {
// column or fields to update
$updateColumn = array_keys($multipleData[0]);
$referenceColumn = $updateColumn[0]; //e.g id
unset($updateColumn[0]);
$whereIn = "";
$q = "UPDATE ".$tableName." SET ";
foreach ( $updateColumn as $uColumn ) {
$q .= $uColumn." = CASE ";
foreach( $multipleData as $data ) {
$q .= "WHEN ".$referenceColumn." = ".$data[$referenceColumn]." THEN "".$data[$uColumn]."" ";
}
$q .= "ELSE ".$uColumn." END, ";
}
foreach( $multipleData as $data ) {
$whereIn .= """.$data[$referenceColumn]."", ";
}
$q = rtrim($q, ", ")." WHERE ".$referenceColumn." IN (". rtrim($whereIn, ", ").")";
// Update
return DB::update(DB::raw($q));
} else {
return false;
}
}
声明：该文观点仅代表作者本人，入门客AI创业平台信息发布平台仅提供信息存储空间服务，如有疑问请联系rumenke@qq.com。
- 上一篇： laravel excel循环导出
- 下一篇： Laravel Excel导出xls乱码
