使用准备好的语句一次插入多行 [英] Inserting multiple rows at once with prepared statements
本文介绍了使用准备好的语句一次插入多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想知道如何通过准备好的语句在数组中插入多个值.我已经看过这两个问题(这个问题和这另外一个)的问题，但它们似乎并没有执行我正在尝试的操作.这就是我所拥有的:
I would like to know how can I insert multiple values in an array via prepared statements. I've looked at these two (this question and this other one ) questions but they don't seem to do what I'm trying. This is what I have:
$stmt = $this->dbh->prepare("INSERT INTO
t_virtuemart_categories_en_gb
(category_name, virtuemart_category_id)
VALUES
(:categoryName, :categoryId)
;");
foreach($this->values as $insertData){
$categoryName = $insertData['categoryName'];
$categoryId = $insertData['categoryId'];
$stmt->bindParam(':categoryName', $categoryName);
$stmt->bindParam(':categoryId', $categoryId);
$stmt->execute();
}
我尝试将prepare行放置在foreach循环内部和外部，但是它仅将第一个键添加到数组中，我不明白为什么.
I tried placing the prepare line inside the foreach loop and outside, but it only adds the first key in the array, and I don't understand why.
这是我的Connection.php文件:
<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
function connectDB ($hostname, $username, $password){
$dbh = new PDO("mysql:host=$hostname;dbname=test", $username, $password);
return $dbh;
}
try {
$dbh = connectDB ($hostname, $username, $password);
} catch(PDOException $e) {
echo $e->getMessage();
}
还有我的Import.php文件:
And my Import.php file:
<?php
class Import{
public function __construct($dbh, $values) {
$this->dbh = $dbh;
$this->values = $values;
}
public function importData() {
$stmt = $this->dbh->prepare("INSERT INTO
t_virtuemart_categories_en_gb
(category_name, virtuemart_category_id)
VALUES
(:categoryName, :categoryId)
;");
foreach($this->values as $insertData){
$categoryName = $insertData['categoryName'];
$categoryId = $insertData['categoryId'];
$stmt->bindParam(':categoryName', $categoryName);
$stmt->bindParam(':categoryId', $categoryId);
$stmt->execute();
}
}
}
推荐答案
工作原理:
仅使用一个INSERT sql语句添加多个记录，这些记录由您的值对定义.为了实现这一点，您必须以以下形式构建相应的sql语句
Use only one INSERT sql statement to add multiple records, defined by your values pairs. In order to achieve this you have to build the corresponding sql statement in the form
INSERT INTO [table-name] ([col1],[col2],[col3],...) VALUES (:[col1],:[col2],:[col3],...), (:[col1],:[col2],:[col3],...), ...
通过遍历您的values数组.
by iterating through your values array.
注释:
- 希望您能全部理解.我尽我所能评论了.一世 没有测试它，但它应该可以工作.也许是答案，我写了一个 不久前将为您提供有关结构化的更多想法 数据访问类/功能.
- 请勿使用;"在PHP中定义sql语句末尾时.
- 切勿使用一个输入标记来绑定多个值.对于每个要绑定的值，请使用唯一的命名输入标记.
- I hope you'll understand all. I commented as much as I could. I didn't test it, but it should work. Maybe an answer I wrote a short time ago will give you further ideas regarding structuring of data access classes/functions as well.
- Never use ";" at the end of the sql statements when you define them in PHP.
- Never use one input marker to bind multiple values. For each value to bind use a unique named input marker.
祝你好运.
<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
$port = 3306;
try {
// Create a PDO instance as db connection to a MySQL db.
$connection = new PDO(
'mysql:host='. $hostname .';port='.$port.';dbname=test'
, $username
, $password
);
// Assign the driver options to the db connection.
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$connection->setAttribute(PDO::ATTR_PERSISTENT, TRUE);
} catch (PDOException $exc) {
echo $exc->getMessage();
exit();
} catch (Exception $exc) {
echo $exc->getMessage();
exit();
}
Import.php:
<?php
class Import {
/**
* PDO instance as db connection.
*
* @var PDO
*/
private $connection;
/**
*
* @param PDO $connection PDO instance as db connection.
* @param array $values [optional] Values list.
*/
public function __construct(PDO $connection, array $values = array()) {
$this->connection = $connection;
$this->values = $values;
}
/**
* Import data.
*
* @return int Last insert id.
* @throws PDOException
* @throws UnexpectedValueException
* @throws Exception
*/
public function importData() {
/*
* Values clauses list. Each item will be
* later added to the sql statement.
*
* array(
* 0 => '(:categoryName0, :categoryId0)',
* 1 => '(:categoryName1, :categoryId1)',
* 2 => '(:categoryName2, :categoryId2)',
* )
*/
$valuesClauses = array();
/*
* The list of the input parameters to be
* bound to the prepared statement.
*
* array(
* :categoryName0 => value-of-it,
* :categoryId0 => value-of-it,
* :categoryName1 => value-of-it,
* :categoryId1 => value-of-it,
* :categoryName2 => value-of-it,
* :categoryId2 => value-of-it,
* )
*/
$bindings = array();
/*
* 1) Build a values clause part for each array item,
* like '(:categoryName0, :categoryId0)', and
* append it to the values clauses list.
*
* 2) Append each value of each item to the input
* parameter list.
*/
foreach ($this->values as $key => $item) {
$categoryName = $item['categoryName'];
$categoryId = $item['categoryId'];
// Append to values clauses list.
$valuesClauses[] = sprintf(
'(:categoryName%s, :categoryId%s)'
, $key
, $key
);
// Append to input parameters list.
$bindings[':categoryName' . $key] = $categoryName;
$bindings[':categoryId' . $key] = $categoryId;
}
/*
* Build the sql statement in the form:
* INSERT INTO [table-name] ([col1],[col2],[col3]) VALUES
* (:[col1],:[col2],:[col3]), (:[col1],:[col2],:[col3]), ...
*/
$sql = sprintf('INSERT INTO t_virtuemart_categories_en_gb (
category_name,
virtuemart_category_id
) VALUES %s'
, implode(',', $valuesClauses)
);
try {
// Prepare the sql statement.
$statement = $this->connection->prepare($sql);
// Validate the preparing of the sql statement.
if (!$statement) {
throw new UnexpectedValueException('The sql statement could not be prepared!');
}
/*
* Bind the input parameters to the prepared statement
* and validate the binding of the input parameters.
*
* -----------------------------------------------------------------------------------
* Unlike PDOStatement::bindValue(), when using PDOStatement::bindParam() the variable
* is bound as a reference and will only be evaluated at the time that
* PDOStatement::execute() is called.
* -----------------------------------------------------------------------------------
*/
foreach ($bindings as $key => $value) {
// Read the name of the input parameter.
$inputParameterName = is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
// Read the data type of the input parameter.
if (is_int($value)) {
$inputParameterDataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$inputParameterDataType = PDO::PARAM_BOOL;
} else {
$inputParameterDataType = PDO::PARAM_STR;
}
// Bind the input parameter to the prepared statement.
$bound = $statement->bindValue($inputParameterName, $value, $inputParameterDataType);
// Validate the binding.
if (!$bound) {
throw new UnexpectedValueException('An input parameter could not be bound!');
}
}
// Execute the prepared statement.
$executed = $statement->execute();
// Validate the prepared statement execution.
if (!$executed) {
throw new UnexpectedValueException('The prepared statement could not be executed!');
}
/*
* Get the id of the last inserted row.
*/
$lastInsertId = $this->connection->lastInsertId();
} catch (PDOException $exc) {
echo $exc->getMessage();
// Only in development phase !!!
// echo '<pre>' . print_r($exc, TRUE) . '</pre>';
exit();
} catch (Exception $exc) {
echo $exc->getMessage();
// Only in development phase !!!
// echo '<pre>' . print_r($exc, TRUE) . '</pre>';
exit();
}
return $lastInsertId;
}
}
这篇关于使用准备好的语句一次插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
