如何将数组插入带有PHP和PDO的单个MySQL Prepared语句中 [英] 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.

例如:

我想注册计划编号155、165、175和185.

I want to enroll in programid 155, 165, 175, and 185.

我的数组设置很简单:

$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程序insert语句，它将看起来如下:

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 Prepared语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！