SQLSTATE[HY093]:参数号无效:参数未定义 [英] SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

问题描述

// 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]:参数号无效:参数未定义

虽然当 count($matches) == count($values) 在执行之前被调用?

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

这是怎么回事?

推荐答案

您收到此错误:

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

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

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

如果 $matches 包含超过 1 个元素，则插入将失败，因为查询中只引用了 1 个列名(hash)

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

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

If $values & $matches do 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 .

我相信您还需要确保列哈希也具有唯一索引.

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

试试代码这里:

<?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());

?>

你需要稍微调整一下.

我使用的表结构是这里:

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 ;

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

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

这篇关于SQLSTATE[HY093]:参数号无效:参数未定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！