Why Does My PDO Prepared Statement Throw an \'Invalid Parameter Number\' Error?

PDO Exception: Invalid Parameter Number

When attempting to execute the add_persist function, an "SQLSTATE[HY093]: Invalid parameter number" error is encountered. The function inserts data into the persist table and updates the hash value if a duplicate key is found.

Upon examination of the code, it becomes evident that the issue lies in the SQL statement. The statement attempts to bind the :hash parameter twice: once for the INSERT operation and again for the ON DUPLICATE KEY UPDATE operation.

To resolve this error, the SQL statement must be modified to include a unique parameter marker for each value being passed to the statement when it is executed. The modified statement and execution code are as follows:

$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));

According to the PHP documentation, using the same named parameter marker twice in a prepared statement is invalid. Each value must have its own unique parameter marker to avoid this error.

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