Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I’m trying to use a prepared array in a PDO query.

I have the following code :

$wordAr = array([0] => ':1' => 'anglais,' [1] => ':2' => 'espagnol'); #Built dynamically in reality
$createTable = "SELECT * FROM candidates WHERE ";
$createTable .= "CandId IN (SELECT CandId FROM etiquettes WHERE speak = :1)";
$createTable .= "AND CandId IN (SELECT CandId FROM etiquettes WHERE speak = :2)";
$stmt = $dbConn->prepare($createTable);
$stmt->execute(array($wordsAr));
$result = $stmt-fetchall();

This give me the following error : Uncaught PDOException: SQLSTATE[HY093]…(same as title) Usually I would execute my query like this :

$stmt->execute(array(
':1' => 'anglais',
':2' => 'espagnol'));

but this time the array is built dynamically. (I put it in this example how it’s shown with a print_r.

I guess that my issue is how is construct my array but I can’t find how to built it correctly.

My purpose would be that $wordAr is interpreted as the query just above. I hope my explanation were clear enough. As I’m still a noob, sometimes I do not express my needs correctly.

Many thanks by advance for any help, I’m learning every day with it.

Answer

You cannot bind pdo params like. You need to bind each param separately.

$wordAr = array([0] => ':1' => 'anglais,' [1] => ':2' => 'espagnol'); #Built dynamically in reality
$createTable = "SELECT * FROM candidates WHERE ";
$createTable .= "CandId IN (SELECT CandId FROM etiquettes WHERE speak = :1)";
$createTable .= "AND CandId IN (SELECT CandId FROM etiquettes WHERE speak = :2)";
$stmt = $dbConn->prepare($createTable);
//loop and bind each param
foreach ($wordAr as $key => &$val) {
    $stmt->bindParam($key, $val);
}
$stmt->execute();
$result = $stmt-fetchall();