使用 PDO 准备语句插入数组数据 [英] Inserting array data using PDO prepared statements
问题描述
我正在慢慢掌握 PDO 和准备好的声明，但有人可以澄清以下内容:
Im slowly getting the hang of PDO and prepared statements, but could someone please clarify the following:
我知道您可以使用以下代码从数组中插入数据:
I understand you can insert data from an array using the following code:
$values = array('bill', 'ben', 'bob');
$stmt = $db->prepare("INSERT INTO first_page_data(first_name) VALUES(:fname)");
$stmt->bindParam(':fname', $first_name, PDO::PARAM_STR);
foreach ($values as $first_name) {
$stmt->execute();
}
这会将三个名称插入到数据库中的 3 个新行中，这里没有问题！
This will insert the three names into 3 new rows in the database, no problems here!
但是，如果我有一个包含数据库列名的数组和一个包含值的相应数组，是否有一种方法可以在一个准备好的语句中为同一用户插入多个 列，而不是输入(相当单调)一行代码:
However if I have an array containing the database column names and a corresponding array containing the values, is there a way of inserting multiple columns for the same user in one prepared statement rather than typing out the (rather monotonous) line of code:
$stmt = $db->prepare("INSERT INTO first_page_data(first_name,surname,phone_no,email,postcode) VALUES(:fname,:sname,:phone,:email,:postcode)");
IE 在表名后面的括号中，只是有一个数组变量而不是列出列名.
IE in the brackets after the table name, just have an array variable rather than listing out the column names.
此外，如果我在准备好的语句中使用未命名的占位符，是否可以从表单遍历 POST 数组以将值绑定到 ? 占位符?(而不是输入多个 $stmt->bindValue(.......) 行?
Also if I use unnamed placeholders in my prepared statement, can I iterate through the POST array from a form to bind the values to the ? placeholders? (instead of typing multiple $stmt->bindValue(.......) lines?
推荐答案
对于你的另一个问题有没有办法用像我这样的包含数字、字符串和布尔值的表单来做到这一点"，然后我做了一个为您提供快速价值粘合剂类.您所要做的就是确保 PHP 类型是正确的(如果有必要，您可以强制转换它 - 例如 $postcode = (int)$postcode).如果您需要一个整数，例如在限制中，->execute() 上的 KVP 数组将在某些时候失败 - 这不会失败.
For your other question "Is there any way of doing this with a form like mine which contains numerical, string and boolean values", then I've made a quick value binder class for you. All you have to do is make sure that the PHP type is correct (if nessesary, you can cast it - eg $postcode = (int)$postcode). Certain times a KVP array on ->execute() will fail if you need an int, eg in a limit - this will not fail.
用法:
<?php
$stmt = $db->prepare("INSERT INTO first_page_data(first_name,surname,phone_no,email,postcode) VALUES(:fname,:sname,:phone,:email,:postcode)");
PDO_Value_Binder::bindValues($stmt, array(
'fname' => 'Bob', //Will be bound as PDO::PARAM_STR
'sname' => 'Bobson', //Will be bound as PDO::PARAM_STR
'phone' => null, //Will be bound as PDO::PARAM_NULL
'email' => 'my@email.com', //Will be bound as PDO::PARAM_STR
'postcode' => 123 //Will be bound as PDO::PARAM_INT
));
$stmt->execute();
?>
类:
<?php
class PDO_Value_Binder {
private static $pdoTypes = array(
'boolean' => PDO::PARAM_BOOL,
'integer' => PDO::PARAM_INT,
'double' => PDO::PARAM_INT,
'string' => PDO::PARAM_STR,
'NULL' => PDO::PARAM_NULL
);
public static function bindValue($statement, $paramName, $value, $pdoType = null) {
if ($paramName[0] != ':') {
$paramName = ':' . $paramName; //Add colon in case we forgot
}
if (empty($pdoType)) {
$valueType = gettype($value); //Get the type of $value to match
if (isset(self::$pdoTypes[$valueType])) {
$pdoType = self::$pdoTypes[$valueType]; //We know this
} else {
$value = print_r($value, true); //Convert to string
$pdoType = PDO::PARAM_STR; //Default to a string
}
}
return $statement->bindValue($paramName, $value, $pdoType);
}
public static function bindValues($statement, $paramKVP, $pdoType = null) {
$return = true;
foreach ($paramKVP as $paramName => $value) {
$return = self::bindValue($statement, $paramName, $value, $pdoType) && $return;
}
return $return;
}
}
?>
这篇关于使用 PDO 准备语句插入数组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
