Why Does My PHP PDO Prepared Statement Throw 'Invalid Parameter Number'?

PDO Exception: Invalid Parameter Number in PHP

Encountering the error "SQLSTATE[HY093]: Invalid parameter number" when attempting to use PHP's PDO? The underlying cause may be related to parameter usage in prepared statements.

Problem Description:

In the provided code snippet:

$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash";

There's a missing ":hash2" parameter marker in the UPDATE clause. This discrepancy leads to the PDO exception because PDO requires unique parameter markers for each value to be bound in the statement.

Solution:

Adjust the prepared statement's SQL query to the following:

$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash2";

Correspondingly, modify the execute() statement to include the ":hash2" parameter marker:

$stm->execute(
    array(
        ":user_id" => $user_id,
        ":hash" => $hash,
        ":expire" => $future,
        ":hash2" => $hash
    )
);

Background:

PDO's prepare() method requires unique parameter markers to bind values when calling execute(). Using repeated parameter markers is disallowed, and assigning multiple values to the same parameter is not supported in clauses like the "IN()" clause.

The above is the detailed content of Why Does My PHP PDO Prepared Statement Throw 'Invalid Parameter Number'?. For more information, please follow other related articles on the PHP Chinese website!