PHP PDO：处理动态SQL时参数绑定顺序的探讨与解决方案

// 1. 定义带有占位符的SQL语句
$sql = "SELECT COUNT(*) FROM users WHERE username = :username";

// 2. 预处理SQL语句，获取PDOStatement对象
$statement = $databaseConnection->prepare($sql);

// 3. 绑定参数
$username = "example_user";
$statement->bindParam(":username", $username, PDO::PARAM_STR);

// 4. 执行语句
$statement->execute();

// 5. 获取结果
$count = $statement->fetchColumn();
echo "用户数量: " . $count;

// 初始SQL语句
$sql = "UPDATE users SET suspended = :newsuspensionsetting";
$params = [
    ':newsuspensionsetting' => $newSuspensionSetting,
    ':permanentidofusertochange' => $permanentIDOfUserToChange
];

if ($newUsernameHasBeenSet) {
    $sql .= ", username = :newusername";
    $params[':newusername'] = $newUsername;
}

if ($newPasswordHasBeenSet) {
    $newPassword = password_hash($newPassword, PASSWORD_DEFAULT);
    $sql .= ", password = :newpassword";
    $params[':newpassword'] = $newPassword;
}

$sql .= " WHERE permanent_id = :permanentidofusertochange";

// 此时SQL语句已完整构建，参数也已收集完毕
$statement = $databaseConnection->prepare($sql);

// 遍历参数数组进行绑定
foreach ($params as $placeholder => $value) {
    // 自动判断参数类型，或根据需要显式指定
    $type = PDO::PARAM_STR; // 默认字符串类型
    if (is_int($value)) {
        $type = PDO::PARAM_INT;
    } elseif (is_bool($value)) {
        $type = PDO::PARAM_BOOL;
    } elseif (is_null($value)) {
        $type = PDO::PARAM_NULL;
    }
    $statement->bindValue($placeholder, $value, $type);
}

$statement->execute();

<?php

// 假设 $databaseConnection 已经是一个有效的 PDO 实例
// $databaseConnection = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
// $databaseConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 模拟传入的数据
$newSuspensionSetting = 1; // 假设为整数
$permanentIDOfUserToChange = 'user_abc_123';
$newUsernameHasBeenSet = true;
$newUsername = 'new_user_name';
$newPasswordHasBeenSet = true;
$newPassword = 'secure_password_123'; // 原始密码

// 1. 初始化SQL语句和参数数组
$sql = "UPDATE users SET suspended = :newsuspensionsetting";
$params = [
    ':newsuspensionsetting' => $newSuspensionSetting,
];

// 2. 根据条件动态构建SQL和收集参数
if ($newUsernameHasBeenSet) {
    $sql .= ", username = :newusername";
    $params[':newusername'] = $newUsername;
}

if ($newPasswordHasBeenSet) {
    // 对密码进行哈希处理
    $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
    $sql .= ", password = :newpassword";
    $params[':newpassword'] = $hashedPassword;
}

// 添加WHERE子句及其参数
$sql .= " WHERE permanent_id = :permanentidofusertochange";
$params[':permanentidofusertochange'] = $permanentIDOfUserToChange;

// 3. 预处理完整的SQL语句
try {
    $statement = $databaseConnection->prepare($sql);

    // 4. 遍历参数数组，使用 bindValue 绑定参数
    foreach ($params as $placeholder => $value) {
        // 自动判断参数类型，提高灵活性。也可以根据业务逻辑显式指定。
        $type = PDO::PARAM_STR;
        if (is_int($value)) {
            $type = PDO::PARAM_INT;
        } elseif (is_bool($value)) {
            $type = PDO::PARAM_BOOL;
        } elseif (is_null($value)) {
            $type = PDO::PARAM_NULL;
        }
        $statement->bindValue($placeholder, $value, $type);
    }

    // 5. 执行语句
    $statement->execute();

    echo "用户数据更新成功！影响行数：" . $statement->rowCount() . "\n";

} catch (PDOException $e) {
    echo "数据库操作失败: " . $e->getMessage() . "\n";
    // 实际应用中应记录错误而非直接输出
}

?>