使用带有 PDO 的数组更新多个 MySQL 表列

2022-01-17 00:00:00 sql-update php mysql pdo

我正在尝试将所有 MySQL 连接从旧的 mysql_query 切换到 PDO.我正在尝试使用不同的数组更新 MySQL 表的多行和多列，但收到以下错误:

I'm trying to switch all my MySQL connections from the old mysql_query to PDOs. I'm trying to update multiple rows and columns of a MySQL table using different arrays and I'm receiving the following error:

[42000]: 语法错误或访问冲突:1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册以获取正确的语法，以便在 '(accnt, car, radio, misc) values ('admin', '300.00', '400.00', '10.00') WHERE ID' at line 附近使用1

[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(accnt, car, radio, misc) values ('admin', '300.00', '400.00', '10.00') WHERE ID' at line 1

来自以下代码:

$account = $_POST['account'];
$car_lease = $_POST['car_lease'];
$radio_lease = $_POST['radio_lease'];
$misc_lease = $_POST['misc_lease'];
$lease_ID = $_POST['lease_ID'];

//$data = array_map(null,$account,$car_lease,$radio_lease,$misc_lease);
$A = count($lease_ID);

try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $STH = $DBH->prepare('UPDATE lease (accnt, car, radio, misc) values (:account, :car_lease, :radio_lease, :misc_lease) WHERE ID = :lease_ID');
    $i = 0;
    while($i < $A) {
        $STH->bindParam(':account', $account[$i]);
        $STH->bindParam(':car_lease', $car_lease[$i]);
        $STH->bindParam(':radio_lease', $radio_lease[$i]);
        $STH->bindParam(':misc_lease', $misc_lease[$i]);
        $STH->bindParam(':lease_ID', $lease_ID[$i]);
        $STH->execute();
        $i++;
    }
}
catch(PDOException $e) {  
    echo "I'm sorry, but there was an error updating the database.";  
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}

我相信这个问题是由我调用语句句柄的方式引起的，但我不确定我的语法的哪一部分不正确.另外，这是处理这种情况的最佳方法吗?或者有没有更好的方法来更新表中的多行?

I believe this problem is arising from the way I'm calling the statement handle, but I'm not sure what part of my syntax is incorrect. Also, is this the best way of handling such situations? Or is there a better method to update multiple rows in a table?

推荐答案

您混淆了 INSERT 和 UPDATE 语句之间的语法.您需要一个 SET 子句，而不是 VALUES() 列表:

You have confused the syntax between INSERT and UPDATE statements. Instead of a VALUES() list, you need a SET clause:

$STH = $DBH->prepare('
    UPDATE lease 
    SET 
      accnt = :account, 
      car = :car_lease, 
      radio = :radio_lease, 
      misc = :misc_lease 
    WHERE ID = :lease_ID
');

查看 MySQL UPDATE 语法参考与 UPDATE 语句一起使用的完整规范.

Review the MySQL UPDATE syntax reference for the full specification to use with UPDATE statements.

相关文章