php “无效的参数号：未定义参数” 插入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5874383/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Invalid parameter number: parameter was not defined" Inserting data
提问by k to the z
UPDATE
更新
I was making a petty mistake when listing the VALUES. I should have put ":username" and not ":alias". I suppose the answer credit to this question is free reign for anyone who wants it? Or do I delete the question?
我在列出 VALUES 时犯了一个小错误。我应该输入“:username”而不是“:alias”。我想这个问题的答案是任何想要它的人都可以自由支配？或者我删除问题？
ORIGINAL
原来的
I've been using Yii's active record pattern for a while. Now, my project needs to access a different database for one small transaction. I thought the Yii's DAO would be good for this. However, I'm getting a cryptic error.
我已经使用 Yii 的活动记录模式有一段时间了。现在，我的项目需要为一个小事务访问不同的数据库。我认为 Yii 的 DAO 会对此有好处。但是，我收到了一个神秘的错误。
CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
CDbCommand 执行 SQL 语句失败：SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
Here is my code:
这是我的代码：
public function actionConfirmation
{
$model_person = new TempPerson();
$model = $model_person->find('alias=:alias',array(':alias'=>$_GET['alias']));
$connection=Yii::app()->db2;
$sql = "INSERT INTO users (username, password, ssn, surname
, firstname, email, city, country)
VALUES(:alias, :password, :ssn, :surname
, :firstname, :email, :city, :country)";
$command=$connection->createCommand($sql);
$command->bindValue(":username", $model->alias);
$command->bindValue(":password", substr($model->ssn, -4,4));
$command->bindValue(":ssn", $model->ssn);
$command->bindValue(":surname", $model->lastName);
$command->bindValue(":firstname", $model->firstName);
$command->bindValue(":email", $model->email);
$command->bindValue(":city", $model->placeOfBirth);
$command->bindValue(":country", $model->placeOfBirth);
$command->execute();
$this->render('confirmation',array('model'=>$model));
}
This constructs the following query (as seen on the application log):
这将构造以下查询（如应用程序日志中所示）：
INSERT INTO users (username, password, ssn, surname, firstname, email
, city, country)
VALUES(:alias, :password, :ssn, :surname, :firstname, :email, :city, :country);
FYI $model->placeOfBirthis supposed to be in both city and county values. That's not a typo (just a silly thing I have to do).
FYI$model->placeOfBirth应该在市和县值中。这不是一个错字（只是我必须做的一件愚蠢的事情）。
回答by thaddeusmt
Just to provide an answer - because this error is pretty common - here are a few causes:
只是为了提供一个答案 - 因为这个错误很常见 - 这里有几个原因：
1) The :parametername does not match the bind by mistake (typo?). This is what happened here. He has :aliasin the SQL statement, but bound :username. So when the param binding was attempted, Yii/PDO could not find :usernamein the sql statement, meaning it was "one parameter short" and threw an error.
1):parameter名称错误地与绑定不匹配（错别字？）。这就是这里发生的事情。他已经:alias在 SQL 语句中，但是绑定了:username. 所以当试图绑定参数时，Yii/PDO:username在 sql 语句中找不到，这意味着它是“一个参数短”并抛出错误。
2) Completely forgetting to add the bindValue()for a parameter. This is easier to do in Yii other constructs like $critera, where you have an array or params ($criteria->params = array(':bind1'=>'test', ':bind2'=>'test)).
2）完全忘记添加bindValue()参数。这在 Yii 其他结构中更容易做到，例如$critera，您有一个数组或参数 ( $criteria->params = array(':bind1'=>'test', ':bind2'=>'test))。
3) Weird conflicts with CDataProvider Pagination and/or Sorting when using togetherand joins. There is no specific, easy way to characterize this, but when using complex queries in CDataProviders I have had weird issues with parameters getting dropped and this error occurring.
3) 使用togetherand时与 CDataProvider 分页和/或排序的奇怪冲突joins。没有具体的、简单的方法来描述这一点，但是在 CDataProviders 中使用复杂的查询时，我遇到了参数丢失和发生此错误的奇怪问题。
One very helpful way to troubleshoot these issues in Yii is to enable parameter loggingin your config file. Add this to your dbarray in your config file:
在 Yii 中解决这些问题的一种非常有用的方法是在您的配置文件中启用参数日志记录。将此添加到您db的配置文件中的数组中：
'enableParamLogging'=>true,
And make sure the CWebLogRouteroute is set up in your logsection. This will print out the query that gave and error, and all of the parameters it was attempting to bind. Super helpful!
并确保CWebLogRoute在您的log部分中设置了路线。这将打印出给出和错误的查询，以及它试图绑定的所有参数。超级有帮助！
回答by Alan Willms
May be you are trying to bind a param within single quotes instead of letting it does the work for you.
可能是您试图在单引号内绑定参数，而不是让它为您完成工作。
Compare:
相比：
Model::model()->findAll("t.description ilike '%:filter%'", array(':filter' => $filter));
With:
和：
Model::model()->findAll("t.description ilike :filter", array(':filter' => '%' . $filter . '%'));
回答by jtubre
A cause of this error for me not covered above is when you're dealing with a dynamic array of parameters if you unset any params, you need to reindex before passing them in. The brutal part of this is that your error log doesn't show indexes so it looks like everything is right. Eg:
上面没有提到的对我来说这个错误的一个原因是当你处理一个动态的参数数组时，如果你取消设置任何参数，你需要在传入它们之前重新索引。 残酷的部分是你的错误日志没有显示索引，所以看起来一切正常。例如：
SELECT id WHERE x = ?, y = ?, z = ?
Might produce the Log: Invalid parameter number: parameter was not defined with params ("x","y","z")
可能会产生日志：无效的参数编号：参数未用 params ("x","y","z") 定义
This looks like it shouldn't be throwing an error, but if the indexes are something like:
这看起来不应该抛出错误，但是如果索引是这样的：
0 => x, 1 => y, 4 => z
It considers the last parameter undefined because it's looking for key 2.
它认为最后一个参数未定义，因为它正在寻找键 2。
回答by Lars Nystr?m
I got this error when trying to do something like:
尝试执行以下操作时出现此错误：
$stmt = $pdo->prepare("select name from mytable where id = :id");
$stmt->execute([
'id' => $id,
'unusedvar' => $foo, // This row causes the error.
]);
Basically, you can't have unused parameters in the array passed to execute(). Every value in the array passed to execute()must be used in your prepared statement.
基本上，您不能将数组中未使用的参数传递给execute(). 传递给数组中的每个值都execute()必须在准备好的语句中使用。
This is also specified in the docs:
这也在文档中指定：
Binding more values than specified is not possible; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted.
绑定比指定值更多的值是不可能的；如果 input_parameters 中存在的键多于 PDO::prepare() 中指定的 SQL 中的键，则该语句将失败并发出错误。
