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();
        }

    }

原创文章如转载,请注明本文链接: http://newmiracle.cn/?p=1941

发表评论

电子邮件地址不会被公开。 必填项已用*标注