mysql批量更新寫法

mysql批量更新寫法

<pre>

$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' => ''));

}

</pre>

更新多個字段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);

}