問題描述

每當(dāng)我嘗試使用 PDO 插入我的數(shù)據(jù)庫時(shí)，我都會(huì)收到錯(cuò)誤消息.

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);
    }

save 函數(shù)生成查詢字符串和綁定，兩者看起來都是正確的.

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" 
}

每當(dāng)我將查詢放入工作臺(tái)時(shí)，我都沒有問題，但是當(dāng)我嘗試在代碼中運(yùn)行它時(shí)，我收到致命錯(cuò)誤:未捕獲的異常 'PDOException'，消息為 'SQLSTATE[HY093]:無效的參數(shù)編號(hào)'，這讓我感到困惑，因?yàn)榻壎▍?shù)的數(shù)量與綁定鍵和數(shù)字匹配.任何人都可以在這個(gè)問題上啟發(fā)我嗎?

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?

推薦答案

我認(rèn)為這可能是因?yàn)槟谡Z句中對(duì)每個(gè)綁定進(jìn)行了兩次 decared，例如:firstname 出現(xiàn)在 VALUES 子句以及 ON DUPLICATE KEY UPDATE 子句中.

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.

您只向 $stmt->execute 傳遞了 8 個(gè)綁定，但 PDO 正在尋找 16 個(gè).

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

您可以嘗試在 ON DUPLICATE KEY UPDATE 子句中對(duì)它們的命名略有不同，為您提供一個(gè)查詢，例如

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 UPDATEfirstName = :update_firstName,lastName = :update_lastName,username = :update_username,password = :update_password,email = :update_email,isSuperUser = :update_isSuperUser,dateCreated = :update_dateCreated,dateLastModified = :update_dateLastModified;

這篇關(guān)于未捕獲的異常 'PDOException' 帶有消息 'SQLSTATE[HY093]: Invalid parameter number'的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！