如何使用 PHP 和 PDO 将数组插入到单个 MySQL 准备好的语句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4629022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
提问by JM4
During an online enrollment, a customer may select a number of programs which they choose to enroll for. These programs are three digit integers and are stored in an array.
在在线注册期间，客户可以选择他们选择注册的多个程序。这些程序是三位整数并存储在数组中。
For example:
例如：
I want to enroll in programid 155, 165, 175, and 185.
我想注册程序 ID 155、165、175 和 185。
My array is set up as simple as:
我的阵列设置简单如下：
$data = array();
$data[] = 155;
$data[] = 165;
$data[] = 175;
$data[] = 185;
When it comes time to insert this information into the associated table, I also include additional elements from the other part of the enrollment:
当需要将此信息插入关联表时，我还包括来自注册其他部分的其他元素：
For example, if I were doing a SINGLE program insert statement, it would look as follows:
例如，如果我正在执行 SINGLE 程序插入语句，它将如下所示：
$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
$stmt->execute(array($memberid, 155));
I would normally create a simple loop for the array above which would call multiple instances of the sql statement and execute such as:
我通常会为上面的数组创建一个简单的循环，它会调用 sql 语句的多个实例并执行，例如：
for($j = 0; $j < (count($data)-1); $j++) {
$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
$stmt->execute(array($memberid, $data[$j]));
}
I do realize the code above is invalid ( $data[$j] ) but looking for the right way to do the call.
我确实意识到上面的代码无效（ $data[$j] ）但正在寻找正确的调用方式。
I have also been told before that building a single dynamic sql statement is overall better than multiple calls like above. My first pass would be something like:
我之前也被告知构建单个动态 sql 语句总体上比像上面这样的多个调用要好。我的第一遍将是这样的：
$sql = array();
foreach( $data as $row ) {
$sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")';
}
mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));
but with PDO I am not quite sure how this works, especially with placeholders (?).
但是对于 PDO，我不太确定这是如何工作的，尤其是占位符 (?)。
Any suggestions?
有什么建议？
回答by ircmaxell
You could build the query programatically...:
您可以以编程方式构建查询...：
$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
foreach ($data as $row) {
$insertQuery[] = '(?, ?)';
$insertData[] = $memberid;
$insertData[] = $row;
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
回答by Xavier Barbosa
2 solutions
2 解决方案
// multiple queries
$stmt = $pdo->prepare('INSERT INTO table SET memberID=:memberID, programID=:programID, date_added=NOW()');
$data = array(155, 165, 175, 185);
foreach($data as $d) {
$stmt->execute(array(':memberID' => $memberid, ':programID' => $d));
}
And
和
// one query
$data = array(155, 165, 175, 185);
$values = array();
foreach($data as $d) {
$values[] = sprintf('(%d, %d, NOW())', $d, $memberid);
}
$sql = sprintf('INSERT INTO table (memberID, programID, date_added) VALUES %s', implode (', ', $values));
$pdo->exec($sql);
回答by Pierre
What you are looking for is how to do a BULK insert, this is more SQL related than to PDO itself.
您正在寻找的是如何进行批量插入，这与 PDO 本身相比与 SQL 相关更多。
You only have to do the exact same thing than with *_query, build your bulk insert query and your param array side by side.
你只需要做与 *_query 完全相同的事情，并排构建你的批量插入查询和你的 param 数组。
$placeholder = array();
$values = "?, ?, ?, ...";
$args = array();
foreach ($arrays as $array) {
$placeholder[] = $value;
$args[] = $array['col1'];
$args[] = $array['col2'];
$args[] = $array['col3'];
...
}
$sql = "INSERT INTO table (col1, col2, ... ) VALUES ("
. implode('), (', $placeholder)
. ")";
$stmt = $db->prepare($sql);
$db->execute($sql, $args);
This is an ugly but working algorithm, I think.
我认为这是一个丑陋但有效的算法。
