带有消息'SQLSTATE [HY093]的未捕获异常'PDOException'：参数号无效'(Uncaught exception 'PDOExc

带有消息'SQLSTATE [HY093]的未捕获异常'PDOException'：参数号无效'(Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number')

每当我尝试使用PDO插入我的数据库时，我都会收到错误。

public function save($primaryKey = "") { $validate = $this->rules(); if ($validate === true) { $properties = ''; $values = ''; $bindings = array(); $update = ''; foreach ($this as $property => $value){ if ($property === "conn") { continue; } $properties .= $property . ','; $values .= ':' . $property . ','; $update .= $property . ' = :' . $property . ','; $bindings[':'.$property] = $value; } $sql_string = 'INSERT INTO ' . get_class($this) . ' (' . rtrim($properties, ',') . ') '; $sql_string .= 'VALUES (' . rtrim($values, ',') . ') ON DUPLICATE KEY UPDATE ' . rtrim($update, ',') . ';'; $result = $this->executeQuery(NULL, $sql_string, $bindings); $this->buildObject($result); if (!empty($primaryKey)) { $this->$primaryKey = $this->conn->lastInsertId(); } return $result; } else { return $validate; } } public function executeQuery($object, $sql_string, $bindings = null) { $stmt = $this->conn->prepare($sql_string); if (!empty($bindings)) { if (!$stmt->execute($bindings)) {return false;} } else { if (!$stmt->execute()) {return false;} } $result = (!empty($object) ? $stmt->fetchAll(PDO::FETCH_CLASS, $object) : $stmt->fetchAll()); return (($stmt->rowCount() > 0) ? $result : false); }

save函数生成查询字符串和绑定，两者看起来都是正确的。

query = INSERT INTO am_administrator (firstName,lastName,username,password,email,isSuperUser,dateCreated,dateLastModified) VALUES (:firstName,:lastName,:username,:password,:email,:isSuperUser,:dateCreated,:dateLastModified) ON DUPLICATE KEY UPDATE firstName = :firstName,lastName = :lastName,username = :username,password = :password,email = :email,isSuperUser = :isSuperUser,dateCreated = :dateCreated,dateLastModified = :dateLastModified; bindings = array(8) { [":firstName"]=> string(5) "First" [":lastName"]=> string(4) "Last" [":username"]=> string(7) "cova-fl" [":password"]=> string(8) "password" [":email"]=> string(16) "test@testing.com" [":isSuperUser"]=> int(1) "1" [":dateCreated"]=> string(19) "2016-05-11 02:40:15" [":dateLastModified"]=> string(19) "2016-05-11 02:40:15" }

每当我将查询放入工作台时我都没有问题，但是当我尝试在代码中运行它时，我得到致命错误：未捕获的异常'PDOException'，消息'SQLSTATE [HY093]：无效的参数号'因为绑定的数量而让我感到困惑params匹配绑定键和数字。 谁能在这个问题上给我启发？

I'm receiving an error whenever I attempt to insert into my database using PDO.

public function save($primaryKey = "") { $validate = $this->rules(); if ($validate === true) { $properties = ''; $values = ''; $bindings = array(); $update = ''; foreach ($this as $property => $value){ if ($property === "conn") { continue; } $properties .= $property . ','; $values .= ':' . $property . ','; $update .= $property . ' = :' . $property . ','; $bindings[':'.$property] = $value; } $sql_string = 'INSERT INTO ' . get_class($this) . ' (' . rtrim($properties, ',') . ') '; $sql_string .= 'VALUES (' . rtrim($values, ',') . ') ON DUPLICATE KEY UPDATE ' . rtrim($update, ',') . ';'; $result = $this->executeQuery(NULL, $sql_string, $bindings); $this->buildObject($result); if (!empty($primaryKey)) { $this->$primaryKey = $this->conn->lastInsertId(); } return $result; } else { return $validate; } } public function executeQuery($object, $sql_string, $bindings = null) { $stmt = $this->conn->prepare($sql_string); if (!empty($bindings)) { if (!$stmt->execute($bindings)) {return false;} } else { if (!$stmt->execute()) {return false;} } $result = (!empty($object) ? $stmt->fetchAll(PDO::FETCH_CLASS, $object) : $stmt->fetchAll()); return (($stmt->rowCount() > 0) ? $result : false); }

The save function generates both the query string and the bindings which both seem correct.

query = INSERT INTO am_administrator (firstName,lastName,username,password,email,isSuperUser,dateCreated,dateLastModified) VALUES (:firstName,:lastName,:username,:password,:email,:isSuperUser,:dateCreated,:dateLastModified) ON DUPLICATE KEY UPDATE firstName = :firstName,lastName = :lastName,username = :username,password = :password,email = :email,isSuperUser = :isSuperUser,dateCreated = :dateCreated,dateLastModified = :dateLastModified; bindings = array(8) { [":firstName"]=> string(5) "First" [":lastName"]=> string(4) "Last" [":username"]=> string(7) "cova-fl" [":password"]=> string(8) "password" [":email"]=> string(16) "test@testing.com" [":isSuperUser"]=> int(1) "1" [":dateCreated"]=> string(19) "2016-05-11 02:40:15" [":dateLastModified"]=> string(19) "2016-05-11 02:40:15" }

Whenever I put the query into workbench I have no problems, but when trying to run it in code I get Fatal Error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' which confuses me since the number of bindings params matches the bindings keys and nummbers. Can anyone enlighten me on this issue?

最满意答案

我想这可能是因为你在语句中已经两次判断每个绑定，例如:firstname出现在VALUES子句中以及ON DUPLICATE KEY UPDATE子句中。

您只将8个绑定传递给$stmt->execute但PDO正在寻找16。

您可以尝试在ON DUPLICATE KEY UPDATE子句中稍微区别它们，为您提供查询，例如

INSERT INTO am_administrator (firstName,lastName,username,password,email,isSuperUser,dateCreated,dateLastModified) VALUES (:firstName,:lastName,:username,:password,:email,:isSuperUser,:dateCreated,:dateLastModified) ON DUPLICATE KEY UPDATE firstName = :update_firstName,lastName = :update_lastName,username = :update_username,password = :update_password,email = :update_email,isSuperUser = :update_isSuperUser,dateCreated = :update_dateCreated,dateLastModified = :update_dateLastModified;

I think this might be because you have decared each binding twice in the statement e.g. :firstname appears in the VALUES clause as well as the ON DUPLICATE KEY UPDATE clause.

You only pass 8 bindings to the $stmt->execute but PDO is looking for 16.

You could try naming them slightly different in the ON DUPLICATE KEY UPDATE clause giving you a query such as e.g.

INSERT INTO am_administrator (firstName,lastName,username,password,email,isSuperUser,dateCreated,dateLastModified) VALUES (:firstName,:lastName,:username,:password,:email,:isSuperUser,:dateCreated,:dateLastModified) ON DUPLICATE KEY UPDATE firstName = :update_firstName,lastName = :update_lastName,username = :update_username,password = :update_password,email = :update_email,isSuperUser = :update_isSuperUser,dateCreated = :update_dateCreated,dateLastModified = :update_dateLastModified;