php SQLSTATE[HY093]：无效的参数号：未定义参数

Question

提问by Griff

// BUILD VALUES
$count = count($matches);
for($i = 0; $i < $count; ++$i) {
    $values[] = '(?)';
}
// INSERT INTO DATABASE
$q = $this -> dbc -> prepare("INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash = hash");
$q -> execute($matches);

The code above fails with the following error

上面的代码失败并出现以下错误

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

SQLSTATE[HY093]：无效的参数号：未定义参数

Although when count($matches) == count($values)just before execute is called?

虽然在count($matches) == count($values)调用 execute 之前？

What is going on here?

这里发生了什么？

Answer 1

回答by Haroon

This error you are receiving :

您收到此错误：

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

SQLSTATE[HY093]：无效的参数号：未定义参数

is because the number of elements in $values& $matchesis not the same or $matchescontains more than 1 element.

是因为$values& 中的元素数量$matches不相同或$matches包含超过 1 个元素。

If $matchescontains more than 1 element, than the insert will fail, because there is only 1 column name referenced in the query(hash)

如果$matches包含 1 个以上的元素，则插入将失败，因为 query( hash) 中只引用了 1 个列名

If $values& $matchesdo not contain the same number of elements then the insert will also fail, due to the query expecting x params but it is receiving y data $matches.

如果$values&$matches不包含相同数量的元素，则插入也将失败，因为查询需要 x 参数但它正在接收 y 数据$matches。

I believe you will also need to ensure the column hash has a unique index on it as well.

我相信您还需要确保列哈希也有一个唯一的索引。

Try the code here:

试试这里的代码：

<?php

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = '';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=test", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }


$matches = array('1');
$count = count($matches);
for($i = 0; $i < $count; ++$i) {
    $values[] = '?';
}

// INSERT INTO DATABASE
$sql = "INSERT INTO hashes (hash) VALUES (" . implode(', ', $values) . ") ON DUPLICATE KEY UPDATE hash='hash'";
$stmt = $dbh->prepare($sql);
$data = $stmt->execute($matches);

//Error reporting if something went wrong...
var_dump($dbh->errorInfo());

?>

You will need to adapt it a little.

你需要稍微调整一下。

Table structure I used is here:

我使用的表结构在这里：

CREATE TABLE IF NOT EXISTS `hashes` (
  `hashid` int(11) NOT NULL AUTO_INCREMENT,
  `hash` varchar(250) NOT NULL,
  PRIMARY KEY (`hashid`),
  UNIQUE KEY `hash1` (`hash`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Code was run on my XAMPP Server which is using PHP 5.3.8 with MySQL 5.5.16.

代码在我的 XAMPP 服务器上运行，该服务器使用 PHP 5.3.8 和 MySQL 5.5.16。

I hope this helps.

我希望这有帮助。

Answer 2

回答by Danny F

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

SQLSTATE[HY093]：无效的参数号：未定义参数

Unfortunately this error is not descriptive for a range of different problems related to the same issue - a binding error. It also does not specify where the error is, and so your problem is not necessarily in the execution, but the sql statement that was already 'prepared'.

不幸的是，此错误无法描述与同一问题相关的一系列不同问题 - 绑定错误。它也没有指定错误在哪里，所以你的问题不一定在执行中，而是已经“准备好”的sql语句。

These are the possible errors and their solutions:

这些是可能的错误及其解决方案：

There is a parameter mismatch - the number of fields does not match the parameters that have been bound. Watch out for arrays in arrays. To double check - use var_dump($var). "print_r" doesn't necessarily show you if the index in an array is another array (if the array has one value in it), whereas var_dumpwill.
You have tried to bind using the same binding value, for example: ":hash" and ":hash". Every index has to be unique, even if logically it makes sense to use the same for two different parts, even if it's the same value. (it's similar to a constant but more like a placeholder)
If you're binding more than one value in a statement (as is often the case with an "INSERT"), you need to bindParam and then bindValue to the parameters. The process here is to bind the parameters to the fields, and then bind the values to the parameters.
```
// Code snippet
$column_names = array();
$stmt->bindParam(':'.$i, $column_names[$i], $param_type);
$stmt->bindValue(':'.$i, $values[$i], $param_type);
$i++;
//.....
```
When binding values to column_names or table_names you can use `` but its not necessary, but make sure to be consistent.
Any value in '' single quotes is always treated as a string and will not be read as a column/table name or placeholder to bind to.

存在参数不匹配 - 字段数与已绑定的参数不匹配。注意数组中的数组。要仔细检查 - 使用var_dump($var)。“ print_r”不一定会显示数组中的索引是否是另一个数组（如果数组中有一个值），而var_dump会。
您尝试使用相同的绑定值进行绑定，例如：":hash" 和 ":hash"。每个索引都必须是唯一的，即使在逻辑上对两个不同的部分使用相同的索引是有意义的，即使它是相同的值。（它类似于一个常量，但更像是一个占位符）
如果您在一条语句中绑定多个值（“INSERT”通常就是这种情况），您需要先将 bindParam 和 bindValue 绑定到参数。这里的过程是先将参数绑定到字段，然后再将值绑定到参数。
```
// Code snippet
$column_names = array();
$stmt->bindParam(':'.$i, $column_names[$i], $param_type);
$stmt->bindValue(':'.$i, $values[$i], $param_type);
$i++;
//.....
```
将值绑定到 column_names 或 table_names 时，您可以使用 `` 但不是必需的，但请确保保持一致。
'' 单引号中的任何值始终被视为字符串，不会被读取为要绑定到的列/表名称或占位符。

Answer 3

回答by codefuncode

I understand that the answer was useful however for some reason it does not work for me however I have moved the situation with the following code and it is perfect

我知道答案很有用，但是由于某种原因它对我不起作用，但是我已经使用以下代码移动了情况并且它是完美的

    <?php

$codigoarticulo = $_POST['codigoarticulo'];
$nombrearticulo = $_POST['nombrearticulo'];
$seccion        = $_POST['seccion'];
$precio         = $_POST['precio'];
$fecha          = $_POST['fecha'];
$importado      = $_POST['importado'];
$paisdeorigen   = $_POST['paisdeorigen'];
try {

  $server = 'mysql: host=localhost; dbname=usuarios';
  $user   = 'root';
  $pass   = '';
  $base   = new PDO($server, $user, $pass);

  $base->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $base->query("SET character_set_results = 'utf8',
                     character_set_client = 'utf8',
                     character_set_connection = 'utf8',
                     character_set_database = 'utf8',
                     character_set_server = 'utf8'");

  $base->exec("SET character_set_results = 'utf8',
                     character_set_client = 'utf8',
                     character_set_connection = 'utf8',
                     character_set_database = 'utf8',
                     character_set_server = 'utf8'");

  $sql = "
  INSERT INTO productos
  (CóDIGOARTíCULO, NOMBREARTíCULO, SECCIóN, PRECIO, FECHA, IMPORTADO, PAíSDEORIGEN)
  VALUES
  (:c_art, :n_art, :sec, :pre, :fecha_art, :import, :p_orig)";
// SE ejecuta la consulta ben prepare
  $result = $base->prepare($sql);
//  se pasan por parametros aqui
  $result->bindParam(':c_art', $codigoarticulo);
  $result->bindParam(':n_art', $nombrearticulo);
  $result->bindParam(':sec', $seccion);
  $result->bindParam(':pre', $precio);
  $result->bindParam(':fecha_art', $fecha);
  $result->bindParam(':import', $importado);
  $result->bindParam(':p_orig', $paisdeorigen);
  $result->execute();
  echo 'Articulo agregado';
} catch (Exception $e) {

  echo 'Error';
  echo $e->getMessage();
} finally {

}

?>

php SQLSTATE[HY093]：无效的参数号：未定义参数

提问by Griff

回答by Haroon

回答by Danny F

回答by codefuncode

相关推荐

最近更新

标签

php SQLSTATE[HY093]：无效的参数号：未定义参数

提问by Griff

回答by Haroon

回答by Danny F

回答by codefuncode

相关推荐

php 如果 $_POST 为空函数

php 如何解决“致命错误：找不到‘MySQLi’类”？

无法使用 php 连接到 mysql

使用 php 更改 css 值

相关推荐

最近更新

标签