是否有一种更安全的方法来使用PDO，同时避免不必要的查询?

I'm using code similar to the one below to insert into a mysql DB (using PDO.) The aim was to insert data using only one query. Unfortunately it defeats the purpose of using PDO because it doesn't make use of bindValue or param. I want to write the code so that it is safer but still avoids unnecessary queries. Can anyone recommend how to do this?

我正在使用类似下面的代码插入到一个mysql DB中(使用PDO)。其目的是仅使用一个查询插入数据。不幸的是，它违背了使用PDO的目的，因为它没有使用bindValue或param。我希望编写代码，以便它更安全，但仍然避免不必要的查询。有人能推荐一下吗?

NOTE1: The number of questions in the set can change each time the program is accessed (ie $totalQ can be different each time it is run).

NOTE1:每次访问程序时，集合中的问题数量都会发生变化(即每次运行时$totalQ可能会不同)。

try 
{
    for ($i=0; $i<$totalQ; $i++)
    { 
        $stqid[$i][0]=$lastInsertValue;     //instance         PDO::PARAM_INT
        $stqid[$i][1]=$jqid[$i][0];          //question number  PDO::PARAM_INT
        $stqid[$i][2]=$jqid[$i][5];          //result           PDO::PARAM_INT
        $stqid[$i][3]=$jqid[$i][3];          //question start   PDO::PARAM_STR 
        $stqid[$i][4]=$jqid[$i][4];          //question finish  PDO::PARAM_STR 
    }

    $values = array();
    foreach ($stqid as $rowValues) 
    {
        foreach ($rowValues as $key => $rowValue) 
        {
            $rowValues[$key] = $rowValues[$key];  
        }

        $values[] = "(" . implode(', ', $rowValues) . ")";
    }

    $count = $dbh->exec("INSERT INTO results(instance, qid, result, start, finish) VALUES  ".implode (', ', $values));  
    $dbh = null;
}

NOTE2: Commas in the time format for start and finish can cause errors with the implode statement. I've just added them so you can see what I'm trying to achieve.

注释2:开始和结束的时间格式的逗号会导致内爆语句的错误。我刚刚添加了它们，这样你就能看到我想要实现的目标。

Any help would be much appreciated. Thanks.

非常感谢您的帮助。谢谢。

EDIT: While I chose chris' answer, I'm very grateful to Alix Axel for his advice. It helped a lot, thank you!

编辑:当我选择克里斯的答案时，我非常感谢Alix Axel的建议。它帮了大忙，谢谢!

2 个解决方案

#1

Untested. Still uses prepared statements.

未测试。仍然使用准备好的语句。

$numColumns = 5; //or $numColumns = count($stqid[0]);
$rowPlaceholder = join(', ', array_fill(0, $numColumns, '?'));
$rowPlaceholders = array_fill(0, $totalQ, "($rowPlaceholder)");
echo $sql = "INSERT INTO results(instance, qid, result, start, finish) VALUES " . join(", \n", $rowPlaceholders);
$flat = call_user_func_array('array_merge', $stqid);
$stmt = $dbh->prepare($sql);
$stmt->execute($flat);

#2

How about something like this:

比如这样:

try 
{
    for ($i=0; $i<$totalQ; $i++)
    { 
        $stqid[$i][0]=$lastInsertValue;     //instance         PDO::PARAM_INT
        $stqid[$i][1]=$jaid[$i][0];          //question number  PDO::PARAM_INT
        $stqid[$i][2]=$jaid[$i][5];          //result           PDO::PARAM_INT
        $stqid[$i][3]=$jqid[$i][3];          //question start   PDO::PARAM_STR 
        $stqid[$i][4]=$jqid[$i][4];          //question finish  PDO::PARAM_STR 
    }

    $values = null;

    foreach ($stqid as $rowValues) 
    {
        $values .= vsprintf('(%s, %s, %s, %s, %s) ', array_map(array($dbh, 'quote'), $rowValues));
    }

    $count = $dbh->exec('INSERT INTO results (instance, qid, result, start, finish) VALUES ' . rtrim($values) . ';');  
    $dbh = null;
}

#1