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

本文介绍了使用命名占位符时，PHP / SQL插入错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下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.