php - 消息为“SQLSTATE [HY093]:无效的参数号”的未捕获异常“PDOException”

每当我尝试使用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);
    }

保存功能会生成查询字符串和绑定，它们看起来都正确。

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) "[e-mail]"
[":isSuperUser"]=> int(1) "1"
[":dateCreated"]=> string(19) "2016-05-11 02:40:15"
[":dateLastModified"]=> string(19) "2016-05-11 02:40:15"
}

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

最佳答案

我认为这可能是因为您在例如: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;

关于php - 消息为“SQLSTATE [HY093]:无效的参数号”的未捕获异常“PDOException”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37173386/