Why Does My PHP PDO Prepared Statement Throw 'SQLSTATE[HY093]: Invalid Parameter Number'?

<h2>PHP PDOException: "SQLSTATE[HY093]: Invalid Parameter Number"</h2>

Debugging a PHP PDOException can be challenging, especially when encountering the "SQLSTATE[HY093]: Invalid parameter number" error. This error often indicates an incorrect use of named parameters in a prepared statement.

Consider the following PHP code:

function add_persist($db, $user_id) {
    $hash = md5("per11".$user_id."sist11".time());
    $future = time()+(60*60*24*14);
    $sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash";
    $stm = $db->prepare($sql);
    $stm->execute(array(":user_id" => $user_id, ":hash" => $hash, ":expire" => $future));
    return $hash;
}

When executing this function, the error "SQLSTATE[HY093]: Invalid parameter number" may occur. The root cause lies in the line where the execute() method is called. Specifically, the ON DUPLICATE KEY UPDATE hash=:hash clause uses the same parameter name, ":hash", for both the insert and update operations.

To resolve this issue, you need to provide a unique parameter name for the hash value in the update clause. Here's the corrected 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)
);

By providing a unique parameter name, ":hash2", for the hash value in the update clause, you resolve the "SQLSTATE[HY093]: Invalid parameter number" error. This ensures that the PDO driver can correctly map parameters to the prepared SQL statement.

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