PeaceAnd Cube PeaceAnd Cube - 3 months ago 14
PHP Question

Dynamic sql query - number of bound variables does not match number of tokens

I'm trying to create a dynamic query according to the information it gets.

When $query2 is for example: 'type' => 'PvP', 'online' => 'Premium'

And $query is: SELECT * FROM dispserveur WHERE type = :type AND online = :online

This is working,

$req = $bdd->prepare("$query");
$req->execute(array('type' => 'PvP', 'online' => 'Premium'));


But when i use the $query2 variable in the execute, it's not working.

$req = $bdd->prepare("$query"); //C
$req->execute(array($query2));


I get the same error each time.

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


This is the code,

unset($sql);
unset($sql2);

if (isset($type2) AND $type2 != "all") {
$sql[] = " type = :type ";
$sql2[] = " 'type' => '$type2'";

}

if (isset($online2) AND $online2 != "all") {
$sql[] = " online = :online ";
$sql2[] = " 'online' => '$online2'";

}
if (isset($version2) AND $version2 != "all") {
$sql[] = " version LIKE :version ";
$sql2[] = " 'version' => %$version2%";

}

$query = "SELECT * FROM dispserveur";

if (!empty($sql)) {
$query .= ' WHERE ' . implode(' AND ', $sql);
}
if (!empty($sql2)) {
$query2 = implode(', ', $sql2);
}

echo $query;
echo "<br />";
echo $query2;

$req = $bdd->prepare("$query"); //C
$req->execute(array($query2));
while ($red = $req->fetch())
{echo "$red[ip]<br />";}


Thanks for your help !

Answer

The parameter of execute() should be an associative array whose keys match that :paramName. Instead, you're using $query2 which is just a string. That's why your code isn't working.

Change:

$sql2[] = " 'type' => '$type2'";
$sql2[] = " 'online' => '$online2'";
$sql2[] = " 'version' => %$version2%";

to:

$sql2['type']=$type2;
$sql2['online']=>$online2;
$sql2['version']=>"%$version2%";

And later, change

$req->execute(array($query2));

to

$req->execute($sql2);