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

Question

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

Answer 1

It looks like you're trying to build a long (?) Series of "or" comparisons: if (x=1) or (x=2) or (x=3) etc... It may be easier for you to replace it:

 $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 bind the values ​​with:

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

Answer 2

You have a mismatch between the number of related parameters and the number of bindings in SQL. Double check what quantity ? and the number of related parameters match.

In addition, HY093 will appear if you tried to bind a parameter that does not exist:

 $stmt = "INSERT INTO table VALUES (:some_value)"; $stmt->bindValue(':someValue', $someValue, PDO::PARAM_STR); 

See that :some_value does not match :someValue ! Correction:

 $stmt = "INSERT INTO table VALUES (:some_value)"; $stmt->bindValue(':some_value', $someValue, PDO::PARAM_STR); 

Answer 3

Positional parameters in SQL start at 1. You process this by binding $i+1 to the position in your loop.

But then you bind the last parameter for cidades_codigo to the position count($options) , which overwrites the last parameter given in the $ options loop.

You need to bind the last parameter to the position count($options)+1 .

---

FWIW, you don't need bindValue() at all. It's easier to just pass an array of execute() parameters. This is how I will write this function:

 public function getCompaniesByCity(City $city, $options = null) { $database = Connection::getConnection(); $sql = "SELECT * FROM `empresas` WHERE `empresas`.`cidades_codigo` = ?" $params = array(); $params[] = $city->getId(); if ($options) { $sql .= " AND `prods_empresas`.`produtos_codigo` IN (" . join(",", array_fill(1, count($options), "?") . ")"; foreach ((array)$options as $option) { $params[] = $option->getId(); } } $statement = $database->prepare($sql); echo $sql; $statement->execute($params); . . . 

Also, don't forget to check the return value of prepare() and execute() , it will be false if there is an error, and you need to check this and report the error. Or else enable PDO to throw exceptions on error.

Answer 4

I ran into this problem due to the presence of additional entries in the named parameter mappings array passed to PDO :: Statement-> execute ()

 $args=array (":x" => 17 ); $pdo->prepare("insert into foo (x) values (:x)"); $pdo->execute($args); // success $args[':irrelevant']=23; $pdo->execute($args) // throws exception with HY093 

Answer 5

Since you made $i+1 in the loop, so count($options) will be equal to the last $i+1 , which makes a duplication of binding.Try

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