php 警告：PDOStatement::execute()：SQLSTATE[HY093]：无效的参数号：绑定变量的数量与中的标记数量不匹配

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

提问by Thomas

I'm working with PHP PDO and I have the following problem:

我正在使用 PHP PDO，但遇到以下问题：

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

回答by dotancohen

You have a mismatch between the amount of bound parameters and the amount of binds in the SQL. Double check that the amount of ?and the amount of bound parameters is the same.

绑定参数的数量与 SQL 中的绑定数量不匹配。仔细检查?绑定参数的数量和数量是否相同。

Additionally, HY093will show up if you have tried to bind a parameter that does not exist:

此外，HY093如果您尝试绑定不存在的参数，则会显示：

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

See that :some_valuedoes not match :someValue! The fix is:

看到:some_value不匹配:someValue！修复方法是：

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

回答by Marc B

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:

看起来您正在尝试构建一个长（？）系列的“或”比较：if (x=1) or (x=2) or (x=3) etc...。您可能会发现将其替换为以下内容更容易：

$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

如果有 5 个选项，它会给你

 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++
}

回答by Bill Karwin

Positional parameters in SQL start at 1. You're handling this by binding to position $i+1in your $options loop.

SQL 中的位置参数从 1 开始。您通过绑定到$i+1$options 循环中的位置来处理此问题。

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

但是随后您将最后一个参数绑定cidades_codigo到 position count($options)，这会覆盖 $options 循环中设置的最后一个参数。

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

您需要将最后一个参数绑定到 position count($options)+1。

---

---

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

FWIW，你根本不需要bindValue()。将参数数组传递给execute(). 下面是我如何编写这个函数：

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 be sure to check the return value of prepare()and execute(), it will be falseif there's an error, and you need to check for that and report the error. Or else enable PDO to throw exceptions on error.

另外一定要检查prepare()and的返回值，如果有错误execute()就会false出现，你需要检查并报告错误。或者启用 PDO 以在出错时抛出异常。

回答by Dave Crooke

I was running into this problem due to having extra entries in the named parameter mapping array passed to PDO::Statement->execute()

由于在传递给 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

回答by Young

Since you have made $i+1in the loop so count($options)would equal the last $i+1which makes a duplicate binding.Try

由于您已$i+1在循环中进行，因此count($options)将等于最后$i+1一个重复绑定。尝试

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

 $statement->bindValue(count($options)+1, $city->getId());