Why Does Using the Same Named Parameter Twice in a PDO INSERT...ON DUPLICATE KEY UPDATE Query Cause a 'Invalid Parameter Number' Error?

PHP PDOException: Invalid Parameter Number

Problem:

When constructing an INSERT query with an ON DUPLICATE KEY UPDATE clause, using a named parameter marker twice in the execute() method can lead to the "SQLSTATE[HY093]: Invalid parameter number" error.

Solution:

To resolve this issue, assign different parameter markers for each value in the execute() method. Here's the updated code:

$sql = "INSERT INTO persist (user_id, hash, expire)
        VALUES (:user_id, :hash, :expire)
        ON DUPLICATE KEY UPDATE hash=:hash2";
$stm->execute(
    array(":user_id" => $user_id,
          ":hash" => $hash,
          ":expire" => $future,
          ":hash2" => $hash)
);

Explanation:

The PDO documentation states that "You cannot use a named parameter marker of the same name twice in a prepared statement." This is because each value being passed to the query must have a unique parameter marker. By assigning different parameter markers for the updated hash value (:hash2), the issue is resolved.

The above is the detailed content of Why Does Using the Same Named Parameter Twice in a PDO INSERT...ON DUPLICATE KEY UPDATE Query Cause a 'Invalid Parameter Number' Error?. For more information, please follow other related articles on the PHP Chinese website!