使用命名占位符时的PHPSQL插入错误

使用命名占位符时的PHP / SQL插入错误

您

$userData

的语句所绑定的占位符必须完全相同，且不能多于或少于。请参阅

PDOStatement::execute

文档，其内容为“您不能绑定的值不能超过指定的值”。

您需要准备参数以

execute()

完全匹配您的绑定。

array_intersect_key()

如果正确安排阵列，这很容易。我通常将其包装在一个函数中，该函数还将处理前缀，如下所示：

// Adds a prefix to a name for a named bind placeholderfunction prefix($name) {    return ':'.$name;}// like 'prefix()', but for array keysfunction 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 togetherfunction 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);}

这样的抽象的好处是您不必担心绑定参数本身。