使用命名占位符时，PHP / SQL插入错误 [英] PHP/SQL Insert Error when using Named Placeholders

问题描述

我有以下PHP PDO语句：

I have the following PHP PDO statement:

$STH = $this->_db->prepare("INSERT INTO UserDetails (FirstName, LastName, 
            Address, City, County, PostCode, Phone, Mobile, Sex, DOB, 
            FundraisingAim, WeeksAim, LengthsAim, HearAboutID,
            MotivationID, WelcomePackID, ContactPrefID, TitleID) 
            VALUES
            (:firstName, :lastName, :address, :city, :county, :postCode, 
            :phone, :mobile, :sex, :DOB, :fundraisingAim, :weeksAim,
            :lengthsAim, :hearAbout, :motivation,
            :welcomePackPref, :contactPref, :title)");

$STH->execute($userData);

其中， $用户数据是一个关联数组。我仔细检查过的名字，我不明白为什么我收到以下错误：

Where $userData is an associative array. I've double checked the names and I don't understand why I'm getting the following error:

SQLSTATE [HY093]：无效的参数编号：绑定变量的数目不匹配的令牌数量

我已做了哪些愚蠢的错误？

What silly mistake have I made?

推荐答案

您 $用户数据必须完全按你的声明的约束，没有更多的相同的占位符，也没有少。请参见 PDOStatement对象::执行文档，说：你不能比指定绑定多个值的部分。

Your $userData must have exactly the same placeholders bound by your statement, no more and no fewer. See PDOStatement::execute documentation, the part that says "You cannot bind more values than specified".

您需要prepare您的参数的execute（）来完全匹配您的绑定。如果你正确地安排你的数组这是很容易与 array_intersect_key（）。

You need to prepare your argument to execute() to match your binds exactly. This is easy with array_intersect_key() if you arrange your arrays correctly. I usually wrap this in a function which will also take care of prefixing, like below:

// Adds a prefix to a name for a named bind placeholder
function prefix($name) {
    return ':'.$name;
}

// like 'prefix()', but for array keys
function prefix_keys($assoc) {
    // prefix STRING keys
    // Numeric keys not included
    $newassoc = array();
    foreach ($assoc as $k=>$v) {
        if (is_string($k)) {
            $newassoc[prefix($k)] = $v;
        }
    }
    return $newassoc;
}

// given a map of datakeyname=>columnname, and a table name, returns an
// sql insert string with named bind placeholder parameters.
function makeInsertStmt($tablename, $namemap) {
    $binds = array_map('prefix', array_keys($namemap));
    return 'INSERT INTO '.$tablename.' ('.implode(',',$namemap).') VALUES ('
    .implode(',',$binds).')';
}

// returns an array formatted for an `execute()`
function makeBindData($data, $namemap) {
    // $data assoc array, $namemap name->column mapping
    return prefix_keys(array_intersect_key($data, $namemap));
}

// example to demonstrate how these pieces fit together
function RunTestInsert(PDO $pdo, $userData) {
    $tablename = 'UserDetails';
    // map "key in $userData" => "column name"
    // do not include ':' prefix in $userData
    $namemap = array(
      'firstName'       => "FirstName",
      'lastName'        => "LastName",
      'address'         => "Address",
      'city'            => "City",
      'county'          => "County",
      'postCode'        => "PostCode",
      'phone'           => "Phone",
      'mobile'          => "Mobile",
      'sex'             => "Sex",
      'DOB'             => "DOB",
      'fundraisingAim'  => "FundraisingAim",
      'weeksAim'        => "WeeksAim",
      'lengthsAim'      => "LengthsAim",
      'hearAbout'       => "HearAboutID",
      'motivation'      => "MotivationID",
      'welcomePackPref' => "WelcomePackID",
      'contactPref'     => "ContactPrefID",
      'title'           => "TitleID",
    );
    $sql = makeInsertStmt($tablename, $namemap);
    $binddata = makeBindData($userData, $namemap);

    $pstmt = $pdo->prepare($sql);
    $pstmt->execute($binddata);
}

这样一个抽象的好处是，你不必担心绑定参数本身。

The benefit of an abstraction like this is you don't need to worry about the bind parameters themselves.

这篇关于使用命名占位符时，PHP / SQL插入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！