Why Am I Getting a \'SQLSTATE[HY093]: Invalid parameter number\' Error in My PHP PDO Code?

Error: Invalid Parameter Number in PHP PDOException

When attempting to execute the add_persist function, developers may encounter the error "SQLSTATE[HY093]: Invalid parameter number." This error stems from an incorrect parameter binding within the SQL query.

In the provided code, the $sql statement attempts to update a row in the persist table by executing the INSERT and ON DUPLICATE KEY UPDATE commands. However, there is a discrepancy in the parameter bindings within the $stm->execute call. Specifically, the :hash parameter appears twice, but according to PDO documentation, each unique parameter marker must be bound only once to a value.

To resolve this issue, the $sql query should be modified to include a distinct parameter marker for each value being passed, as follows:

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

Additionally, the $stm->execute call should include bindings for both the :hash and :hash2 parameters:

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

By addressing this parameter binding issue, the add_persist function will correctly execute the SQL query without triggering the "Invalid parameter number" error.

The above is the detailed content of Why Am I Getting a 'SQLSTATE[HY093]: Invalid parameter number' Error in My PHP PDO Code?. For more information, please follow other related articles on the PHP Chinese website!