mysql批量更新写法详解数据库

mysql批量更新写法

 $namedmp=filter($_POST['namedmp']);
 $namedsp=filter($_POST['namedsp']);
 $nameula=filter($_POST['nameula']);
 $namenva=filter($_POST['namenva']);
 $namedcrm=filter($_POST['namedcrm']);
 

 $sql='UPDATE wz_chanpinjieshao
 SET titile = CASE id 
 WHEN 1 THEN ?
 WHEN 2 THEN ?
 WHEN 3 THEN ?
 WHEN 4 THEN ?
 WHEN 5 THEN ?
 END
WHERE id IN (1,2,3,4,5)';
 $stmt = /Db::getStmt($sql);
 $isOk=$stmt->execute(array($namedmp,$namedsp,$nameula,$namenva,$namedcrm));
 if($isOk){
 echo json_encode(array('success' => 1, 'msg' => '更新成功', 'data' => ''));
 exit();
 }else{
 echo json_encode(array('success' => 1, 'msg' => '网络繁忙', 'data' => ''));
 exit();
 }

更新多个字段sql语句

UPDATE wz_chanpinjieshao 
 SET title = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 END,
 summary = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 END 
 WHERE id IN (1,2,3,4,5)

下面是封装版

 public function updatechanpinjieshao(){
 $nameupdate_ziduan_list=explode(',',$_POST['nameupdate_ziduan']);
 $namelist=array();
 $duogeziduan='';
 foreach ($nameupdate_ziduan_list as $k=>$v) {
 
 $namedmp = filter($_POST['namedmp_' . $v]);
 array_push($namelist,$namedmp);
 $namedsp = filter($_POST['namedsp_' . $v]);
 array_push($namelist,$namedsp);
 $nameula = filter($_POST['nameula_' . $v]);
 array_push($namelist,$nameula);
 $namenva = filter($_POST['namenva_' . $v]);
 array_push($namelist,$namenva);
 $namedcrm = filter($_POST['namedcrm_' . $v]);
 array_push($namelist,$namedcrm);
 
 $duogeziduan.= $v . ' = CASE id WHEN 1 THEN ? WHEN 2 THEN ? WHEN 3 THEN ? WHEN 4 THEN ? WHEN 5 THEN ? END,';
 }
 $duogeziduan=rtrim($duogeziduan,",");
 
 $sql = 'UPDATE wz_chanpinjieshao SET '.$duogeziduan.' WHERE id IN (1,2,3,4,5)';
 

 $stmt = /Db::getStmt($sql);
 
 $isOk = $stmt->execute($namelist);
 
 if($isOk){
 echo json_encode(array('success' => 1, 'msg' => '更新成功', 'data' => ''));
 exit();
 }else{
 echo json_encode(array('success' => 1, 'msg' => '网络繁忙', 'data' => ''));
 exit();
 }
 
 }