如何使用 PHP 和 PDO 将数组插入到单个 MySQL 准备好的语句中 [英] How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
问题描述
在线注册期间,客户可以选择他们选择注册的多个计划.这些程序是三位整数并存储在一个数组中.
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.
例如:
我想注册程序 ID 155、165、175 和 185.
I want to enroll in programid 155, 165, 175, and 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:
例如,如果我正在执行 SINGLE 程序插入语句,它将如下所示:
For example, if I were doing a SINGLE program insert statement, it would look as follows:
$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
$stmt->execute(array($memberid, 155));
我通常会为上面的数组创建一个简单的循环,它会调用 sql 语句的多个实例并执行,例如:
I would normally create a simple loop for the array above which would call multiple instances of the sql statement and execute such as:
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]));
}
我确实意识到上面的代码无效( $data[$j] )但正在寻找正确的调用方式.
I do realize the code above is invalid ( $data[$j] ) but looking for the right way to do the call.
我之前也被告知构建单个动态 sql 语句总体上比像上面这样的多个调用要好.我的第一遍是这样的:
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 = array();
foreach( $data as $row ) {
$sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")';
}
mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));
但是对于 PDO,我不太确定这是如何工作的,尤其是占位符 (?).
but with PDO I am not quite sure how this works, especially with placeholders (?).
有什么建议吗?
推荐答案
您可以以编程方式构建查询...:
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);
}
这篇关于如何使用 PHP 和 PDO 将数组插入到单个 MySQL 准备好的语句中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!