php - 如何使用 MySQLi 准备好的语句执行多个占位符数量可变的 INSERT？

Question

关于如何使用 MySQLi 准备好的语句 执行多个 INSERT 同时具有可变数量(大约 40 个)的占位符有什么想法吗？

我知道如何使用可变数量的占位符制作准备好的语句:

array_unshift($paramValues, str_repeat('s', count($paramValues)));
call_user_func_array(
    [$statement, 'bind_param'],
    sql::makeValuesReferenced($paramValues)
);

我也知道如何进行多次执行:

$statement->bind_param('i', $id);
for ($id=0, $id<10, ++$id) {
    $statement->execute();
}

但我无法集中精力将这两种方法合并为一种方法。

基本上，我有一个充满数据的数组，我想将这些数据插入到数据库中，而无需手动硬编码变量。我想要函数，我将数组与数据放在一起，函数将负责绑定(bind)和执行。

$data = [
    0 => [a => aaa, b => bbb],
    1 => [a => ccc, b => ddd],
];

(我使用的是 PHP 5.5 和 MySQL 5.5。)

Answer 1

很简单，您只需准备一次 sql，然后多次重复 bind_param() 和 execute()。尽管我认为您已经明白了。

因此使用您的示例输入

$data = [
    0 => [a => aaa, b => bbb],
    1 => [a => ccc, b => ddd],
];

.

// get a list of all the field names
// and also build the question mark list
$fields = '';
$qMarks = '';

foreach ( $data[0] as $field => $val ) {
    $fields .= $field . ',';
    $qMarks .= '?,';
}
rtrim($fields, ',');
rtrim($qMarks, ',');

/* 
   Build the datatype list:

   Replace commas with nothing and ? with s
   THIS WILL ONLY WORK IF YOUR DATATYPES ARE ALL THE SAME

   If you also had the datatypes in your $data array this 
   would obviously work better, or rather be more flexible

   I THINK THIS IS THE FLY IN THE OINTMENT!!
*/

$datatypes = '';
$datatypes = str_replace(array(',','?'),array('','s'),$qMarks);

$sql = "INSERT INTO TABLE table ($fields) VALUES($qMarks)";

$stmt = $db->prepare($sql);


foreach ($data as $row ) {

    $params = array();
    foreach ( $row as $name => $val ) {
        $params[] = $val;
    }

    $stmt->bind_param($datatypes, $params);
    $result = $stmt->execute();

    if ( ! $result ) {
        // You have an error, deal with it here and probably stop the loop
    }
}