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

Invalid Parameter Number Error in PHP PDO

When attempting to execute a prepared statement using PDO, you may encounter the error "SQLSTATE[HY093]: Invalid parameter number." This issue arises due to the incorrect usage of parameter markers.

The provided function add_persist uses the following prepared statement:

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

When binding the parameter values using execute() method, the function uses the following values:

["user_id" => $user_id, "hash" => $hash, "expire" => $future]

However, the prepared statement contains a duplicate parameter marker for hash, which is not allowed by PDO. To resolve this issue, we need to assign a unique parameter marker for each value being passed.

The corrected code would be:

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

$stm = $db->prepare($sql);

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

The additional parameter :hash2 ensures that there are no duplicate parameter markers in the prepared statement, resolving the error.

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