php 批量修改数据

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 假设我们要批量更新用户的年龄
$usersToUpdate = [
    ['id' => 1, 'age' => 30],
    ['id' => 2, 'age' => 31],
    ['id' => 3, 'age' => 32]
];

// 构建批量更新的 SQL 语句
$sql = "UPDATE users SET age = CASE id ";
foreach ($usersToUpdate as $user) {
    $sql .= sprintf("WHEN %d THEN %d ", $user['id'], $user['age']);
}
$sql .= "END WHERE id IN (";
$ids = implode(',', array_column($usersToUpdate, 'id'));
$sql .= "$ids);";

// 执行 SQL 语句
if ($conn->query($sql) === TRUE) {
    echo "批量更新成功";
} else {
    echo "错误: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

<?php
// 开始事务
$conn->begin_transaction();

try {
    // 执行批量更新操作
    $conn->query($sql);

    // 提交事务
    $conn->commit();
    echo "批量更新成功";
} catch (Exception $e) {
    // 回滚事务
    $conn->rollback();
    echo "批量更新失败: " . $e->getMessage();
}
?>