PHP PDOException: "SQLSTATE[HY093]: Invalid parameter number"

Question

I'm getting the error "SQLSTATE[HY093]: Invalid parameter number" when I try to run the below function:

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;
}

I feel like it's something simple that I'm just not catching. Any ideas?

`You cannot use a named parameter marker of the same name twice in a prepared statement.` [http://php.net/manual/en/pdo.prepare.php](http://php.net/manual/en/pdo.prepare.php) — bitWorking, Aug 03 '13 at 02:42
I understand that that post has an answer that answers my question as well, but my question definitely isn't a possible duplicate. — vijrox, Aug 03 '13 at 02:50
In the other question, `fetchAll()` returns nothing because the query failed just as yours. The other author just did not notice it. This is essentially the same problem, so it is worth linking these two. — RandomSeed, Aug 03 '13 at 02:58
yes they are linked in that the answers are the same, but there is no way of knowing that based on the questions — vijrox, Aug 03 '13 at 07:32

score 53 · Accepted Answer · edited Aug 11 '20 at 11:34

53

Try:

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

and

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

Excerpt from the documentation (http://php.net/manual/en/pdo.prepare.php):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

edited Aug 11 '20 at 11:34

Tony

9,672
3
47
75

answered Aug 03 '13 at 02:40

vee

38,255
7
74
78

1

it worked! I'll accept your answer as soon as stack lets me. Why doesn't the original method work? – vijrox Aug 03 '13 at 02:42
@VijayRamamurthy, please read through this: http://php.net/manual/en/pdo.prepare.php – vee Aug 03 '13 at 02:43
What is the difference between your answer and OP's question? – Shafizadeh Nov 18 '15 at 14:22

score 18 · Answer 2 · edited May 23 '17 at 12:10

18

This is one limitation to using PDO. PDO simply acknowledges the number of parameters in the query and the execution and throws an error on any mismatch. If you need to use parameter repetition in your queries, you have to go about it using a workaround

$sql = "insert into persist(user_id, hash, expire) values
    (:user_id, :hash, :value) on duplicate key update
    hash = :hash2";
$stm->execute(array(':user_id' => $user_id, ':hash' => $hash, ':hash2' => $hash,
    ':expire' => $expire));

You can refer to this for a more elaborate workaround - https://stackoverflow.com/a/7604080/1957346

edited May 23 '17 at 12:10

Community

1
1

answered Aug 03 '13 at 02:45

Achrome

7,773
14
36
45

3

Awesome! I spent two days debugging code that worked on everything else except one particular query. The answer was (as mentioned by "vijrox" above in a comment) that you cannot reuse the same named parameter in PDO. I had `UPDATE users SET username = :username, caption = :caption WHERE username = :username`, which will not work because `:username` appears twice. The fix was to rename the second one, ending up with this: `UPDATE users SET username = :username, caption = :caption WHERE username = :user` (the last `:user` is different). – Juha Untinen Apr 27 '16 at 21:34

score 5 · Answer 3 · answered Apr 04 '17 at 05:54

I know this is an old question, however I think it's worth noting that a more appropriate solution would be to avoid clunky workarounds in PHP by leveraging SQL appropriately:

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

This way, you only need to send the value once.

score -2 · Answer 4 · edited Sep 27 '16 at 07:42

-2

$stmt = $con->prepare("INSERT INTO items(Name, Description, Price, Country_Made, Status, Add_Date)  VALUES( :zname, :zdesc, :zprice, :zcountry, zstatus, now())");

$stmt-> execute(array(
   "zname" => $name,
   "zdesc" => $desc,
   "zprice" => $price,
   "zcountry" => $country,
   "zstatus" => $status 
));

edited Sep 27 '16 at 07:42

croxy

4,082
9
28
46

answered Sep 27 '16 at 06:33

Razib

11

Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term educational value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. – Toby Speight Sep 27 '16 at 17:27

PHP PDOException: "SQLSTATE[HY093]: Invalid parameter number"

4 Answers4

Linked

Related