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

code-snippets, data-binding, database, php

Solution

It looks like you're trying to build a long(?) series of 'or' comparisons: `if (x=1) or (x=2) or (x=3) etc...`. You may find it easier to replace it with:

$cnt = count($options);
if ($cnt > 0) {
   $placeholders = str_repeat(', ?', $cnt - 1);
   $sql .= 'WHERE '`prods_empresas`.`produtos_codigo` IN (?' . $placeholders . ')';
}

which, if there were 5 options, would give you

 WHERE prods_empresas.produtos_condigo IN (?, ?, ?, ?, ?)

And then do the values binding with:

$pos = 1;
foreach ($options as $option) {
   $statement->bindValue($pos, $option->getId());
   $pos++
}

Problem

I'm working with PHP PDO and I have the following problem: ``` Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /var/www/site/classes/enterprise.php on line 63 ``` Here is my code: ``` public function getCompaniesByCity(City $city, $options = null) { $database = Connection::getConnection(); if(empty($options)) { $statement = $database->prepare("SELECT * FROM `empresas` WHERE `empresas`.`cidades_codigo` = ?"); $statement->bindValue(1, $city->getId()); } else { $sql = "SELECT * FROM `empresas` INNER JOIN `prods_empresas` ON `prods_empresas`.`empresas_codigo` = `empresas`.`codigo` WHERE "; foreach($options as $option) { $sql .= '`prods_empresas`.`produtos_codigo` = ? OR '; } $sql = substr($sql, 0, -4); $sql .= ' AND `empresas`.`cidades_codigo` = ?'; $statement = $database->prepare($sql); echo $sql; foreach($options as $i => $option) { $statement->bindValue($i + 1, $option->getId()); } $statement->bindValue(count($options), $city->getId()); } $statement->execute(); $objects = $statement->fetchAll(PDO::FETCH_OBJ); $companies = array(); if(!empty($objects)) { foreach($objects as $object) { $data = array( 'id' => $object->codigo, 'name' => $object->nome, 'link' => $object->link, 'email' => $object->email, 'details' => $object->detalhes, 'logo' => $object->logo ); $enterprise = new Enterprise($data); array_push($companies, $enterprise); } return $companies; } } ```

Original source