分享一下php+mysqli批量更新的方法
<?php
const DB_SERVERNAME = '數(shù)據(jù)庫(kù)地址';
const DB_USERNAME = '用戶名';
const DB_PASSWORD = '密碼';
const DB_DBNAME = '庫(kù)名';
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);
}
// 要批量更新的二維數(shù)組
$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 "暫無(wú)更新數(shù)據(jù)"."<br/>";
}
}
}else{
echo '二維數(shù)組為空';
}
$con->close();
}
function updateBatch($table, $values,$str){
// 假設(shè)以 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語(yǔ)句
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, ";
}
//把最后一個(gè),去掉
$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批量添加簡(jiǎn)單易懂】
http://www.reibang.com/p/e3b9f8289c88
希望可以幫助到大家
