PHP PDO能否在调用prepare预处理SQL之前先执行bindParam参数绑定

核心原因 #

PDO的PDOStatement实例（也就是你代码中的$statement对象）只能通过$databaseConnection->prepare()方法调用后生成。你在还未执行prepare()的时候就尝试调用$statement->bindParam()，会直接触发Call to a member function bindParam() on null的致命错误，程序无法正常运行。

动态拼接SQL的正确实现方案 #

你要实现动态构造SQL的需求完全不用强行调整调用顺序，只需要在拼接SQL模板的同时提前存储待绑定的参数列表，预处理完成后统一绑定即可，既符合PDO规范，代码逻辑也同样清晰：

方案1：保留命名参数+显式指定类型 #

// 初始化SQL模板和参数存储数组
$sql = "update users set suspended = :newsuspensionsetting";
$bindParams = [
    ':newsuspensionsetting' => [$newSuspensionSetting, PDO::PARAM_INT]
];

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

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

// 注意拼接where前加空格，避免出现SQL语法错误
$sql .= " where permanent_id = :permanentidofusertochange";
$bindParams[':permanentidofusertochange'] = [$permanentIDOfUserToChange, PDO::PARAM_STR];

// 先预处理得到statement对象
$statement = $databaseConnection->prepare($sql);
// 批量绑定参数
foreach ($bindParams as $paramName => $paramInfo) {
    $statement->bindParam($paramName, $paramInfo[0], $paramInfo[1]);
}
$statement->execute();

方案2：使用位置参数+简化调用 #

如果你不需要为个别参数显式指定类型，也可以直接用位置参数，通过execute()方法直接传入参数数组，代码更简洁：

$sql = "update users set suspended = ?";
$bindParams = [$newSuspensionSetting];

if ($newUsernameHasBeenSet) {
    $sql .= ", username = ?";
    $bindParams[] = $newUsername;
}

if ($newPasswordHasBeenSet) {
    $newPassword = password_hash($newPassword, PASSWORD_DEFAULT);
    $sql .= ", password = ?";
    $bindParams[] = $newPassword;
}

$sql .= " where permanent_id = ?";
$bindParams[] = $permanentIDOfUserToChange;

$statement = $databaseConnection->prepare($sql);
$statement->execute($bindParams);

内容的提问来源于stack exchange，提问作者Zuno