php+mysqli批量更新简单易懂

分享一下php+mysqli批量更新的方法

<?php
const DB_SERVERNAME = '数据库地址';
const DB_USERNAME = '用户名';
const DB_PASSWORD = '密码';
const DB_DBNAME = '库名';

getData();

function getData(){
      $table = '表名';
      $con = @mysqli_connect(DB_SERVERNAME,DB_USERNAME,DB_PASSWORD);
      mysqli_select_db($con,DB_DBNAME);
      $conn = new mysqli(DB_SERVERNAME, DB_USERNAME, DB_PASSWORD, DB_DBNAME);
      if ($conn->connect_error) {
           die("连接失败: " . $conn->connect_error);
      }
      // 要批量更新的二维数组
      $array = [[],[]];
      if(!empty($array)){
            $splitNum = 500;
            foreach(array_chunk($updateArray, $splitNum) as $values){
                $sql = updateBatch($table,  $values, 'today');
                if ($conn->query($sql) === TRUE) {
                   echo " ----更新成功(今日) "."<br/>";
                } else {
                   echo "Error: " . $sql . "<br>" . $conn->error;
                   echo "暂无更新数据"."<br/>";
                }
            }
      }else{
            echo '二维数组为空';
      }
      $con->close();
}

function updateBatch($table, $values,$str){
    // 假设以 app_flag 字段为条件
    $app_flags = '';
    $update_data = array();
    foreach($values as $k => &$v){
        $update_data[$k]['app_flag'] = addslashes($v['app_flag']);
        $update_data[$k]['user_add'] = addslashes($v['user_add']);
        $update_data[$k]['user_sub'] = addslashes($v['user_sub']);

        $app_flags .= addslashes($v['app_flag']).',';
    }
    $sql = "UPDATE " . $table . " SET ";
    //合成sql语句
    foreach ($update_data[0] as $key => $value) {
        $sql .= "`{$key}` = CASE `app_flag` ";
        foreach ($update_data as $newhouse_clicks_key => $newhouse_clicks_value) {

            $sql .= sprintf("WHEN '%s' THEN '%s' ", $update_data[$newhouse_clicks_key]['app_flag'], $newhouse_clicks_value[$key]);

        }
        $sql .= "END, ";
    }

    //把最后一个,去掉
    $sql = substr($sql, 0, strrpos($sql,','));
    //合并所有
    $app_flags = substr($app_flags , 0, strrpos($app_flags,','));
    $app_flags = explode(',',$app_flags);
    $app_flags = implode('\',\'',$app_flags);
    //拼接sql
    $sql .= " WHERE `app_flag` IN ('{$app_flags}') ";

    return $sql;

}

如果还需要了解批量添加的话，可以参考下我的另一篇文章
【php+mysqli批量添加简单易懂】
https://www.jianshu.com/p/e3b9f8289c88
希望可以帮助到大家