PDO - Invalid parameter number

Question

Just recently I've switched to using PDO in PHP/MySQL and transformed some dozens of queries. Most of them worked, however this very easy one throws an exception at $sql->execute()

$sql=$pdo->prepare("SELECT id FROM user WHERE username = :username LIMIT 1");
$sql->execute(array(':username',$username));

PDOStatement::execute() pdostatement.execute SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in ...

After research, I found this link: https://bugs.php.net/bug.php?id=60515

... and therefore tried to change the query to

$sql=$pdo->prepare("SELECT `id` FROM `user` WHERE `username` = :username LIMIT 1");
$sql->execute(array(':username',$username));

But still with the same result. Does anybody see what is obviously wrong or why does this query not work when all others did?

Thank you very much in advance!

Answer 1

This is not bug, you provide two parameters for only one placeholder.

$sql->execute(array(':username',$username));

should be

$sql->execute(array(':username' => $username));

Answer 2

The ':username',$username works only in bindParam() method:

$sql->bindParam(':username', $username, PDO::PARAM_STR);

Take a look here: http://www.php.net/manual/en/pdostatement.bindparam.php

For execute you need to pass a correct array of input-only values:

$sql->execute(array(':username' => $username));

Placeholder:

You can also use this:

$sql->execute(array($username));

But for this you need to change your query to this:

$sql=$pdo->prepare("SELECT `id` FROM `user` WHERE `username` = ? LIMIT 1");    

The ? works as palceholder and take the variables from the array. When you use more placeholder in your SQL statement the function takes all the variables out of the array in it's order.