PHP PDOException：“SQLSTATE[HY093]：无效的参数号”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18028706/
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
PHP PDOException: "SQLSTATE[HY093]: Invalid parameter number"
提问by vijrox
I'm getting the error "SQLSTATE[HY093]: Invalid parameter number" when I try to run the below function:
当我尝试运行以下函数时，出现错误“SQLSTATE[HY093]: Invalid parameter number”：
function add_persist($db, $user_id) {
$hash = md5("per11".$user_id."sist11".time());
$future = time()+(60*60*24*14);
$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash";
$stm = $db->prepare($sql);
$stm->execute(array(":user_id" => $user_id, ":hash" => $hash, ":expire" => $future));
return $hash;
}
I feel like it's something simple that I'm just not catching. Any ideas?
我觉得这很简单，我只是没有抓住。有任何想法吗？
回答by vee
Try:
尝试：
$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash2";
and
和
$stm->execute(array(":user_id" => $user_id, ":hash" => $hash, ":expire" => $future, ":hash2" => $hash));
Excerpt from the documentation (http://php.net/manual/en/pdo.prepare.php):
文档摘录（http://php.net/manual/en/pdo.prepare.php）：
You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.
当您调用 PDOStatement::execute() 时，您必须为您希望传递给语句的每个值包含一个唯一的参数标记。您不能在准备好的语句中两次使用同名的命名参数标记。例如，不能将多个值绑定到单个命名参数，例如 SQL 语句的 IN() 子句。
回答by Achrome
This is one limitation to using PDO. PDO simply acknowledges the number of parameters in the query and the execution and throws an error on any mismatch. If you need to use parameter repetition in your queries, you have to go about it using a workaround
这是使用 PDO 的一个限制。PDO 简单地确认查询和执行中的参数数量，并在任何不匹配时抛出错误。如果您需要在查询中使用参数重复，则必须使用解决方法
$sql = "insert into persist(user_id, hash, expire) values
(:user_id, :hash, :value) on duplicate key update
hash = :hash2";
$stm->execute(array(':user_id' => $user_id, ':hash' => $hash, ':hash2' => $hash,
':expire' => $expire));
You can refer to this for a more elaborate workaround - https://stackoverflow.com/a/7604080/1957346
您可以参考此了解更详细的解决方法 - https://stackoverflow.com/a/7604080/1957346
回答by Duncan
I know this is an old question, however I think it's worth noting that a more appropriate solution would be to avoid clunky workarounds in PHP by leveraging SQL appropriately:
我知道这是一个老问题，但是我认为值得注意的是，更合适的解决方案是通过适当地利用 SQL 来避免 PHP 中的笨拙变通方法：
INSERT INTO `persist` (`user_id`, `hash`, `expire`)
VALUES (:user_id, :hash, :expire)
ON DUPLICATE KEY UPDATE `hash`=VALUES(`hash`)
This way, you only need to send the value once.
这样，您只需要发送一次该值。
回答by Razib
$stmt = $con->prepare("INSERT INTO items(Name, Description, Price, Country_Made, Status, Add_Date) VALUES( :zname, :zdesc, :zprice, :zcountry, zstatus, now())");
$stmt-> execute(array(
"zname" => $name,
"zdesc" => $desc,
"zprice" => $price,
"zcountry" => $country,
"zstatus" => $status
));
