問題描述
我希望使用 PHP PDO 進行多次插入.
I am looking to do multiple inserts using PHP PDO.
我找到的最接近的答案是這個
The closest answer I have found is this one
how-to-insert-an-array-into-a-single-mysql-prepared-statement
但是給出的示例使用 ??而不是真正的占位符.
However the example thats been given uses ?? instead of real placeholders.
我查看了 PHP 文檔站點上的占位符示例
I have looked at the examples on the PHP doc site for place holders
php.net pdo.prepared-statements
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
現(xiàn)在假設我想用數(shù)組實現(xiàn)上述目標
Now lets say I wanted to achieve the above but with an array
$valuesToInsert = array(
0 => array('name' => 'Robert', 'value' => 'some value'),
1 => array('name' -> 'Louise', 'value' => 'another value')
);
對于 PDO 和每個事務的多個插入，我將如何處理?
我想它會從一個循環(huán)開始?
I imagine it would start of with a loop?
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
foreach($valuesToInsert as $insertRow){
// now loop through each inner array to match binded values
foreach($insertRow as $column => value){
$stmt->bindParam(":{$column}", value);
}
}
$stmt->execute();
然而，上述方法不起作用，但希望能證明我試圖實現(xiàn)的目標
However the above does not work but hopefully will demonstrate what im trying to achieve
推薦答案
首先，? 符號是真正的占位符(大多數(shù)驅動程序允許同時使用這兩種語法，位置和命名占位符).其次，準備好的語句只不過是一種將原始輸入注入到 SQL 語句中的工具——SQL 語句本身的語法不受影響.您已經(jīng)擁有所需的所有元素:
First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:
- 如何使用單個查詢插入多行
- 如何動態(tài)生成 SQL
- 如何使用帶有命名占位符的預處理語句.
將它們?nèi)拷Y合起來非常簡單:
It's fairly trivial to combine them all:
$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = [];
$insertData = [];
$n = 0;
foreach ($data as $row) {
$insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
$insertData['memberID' . $n] = $memberid;
$insertData['programID' . $n] = $row;
$n++;
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
這篇關于php PDO使用占位符批量插入多行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！