php绑定动态变量的数量，用于批量插入查询 [英] php binding dynamic number of variables for batch insert query

问题描述

我有一个Web服务，用户可以在其中传递动态数量的问题.

I have a web service where a user passes up a dynamic number of questions.

在php端，我正在使用explode?剔除每个问题.然后，我需要进行批量插入.

On the php side I am using explode with the ? to strip out each question. I then need to do a batch insert.

到目前为止，我所做的如下:

What I've done so far is as follows:

$checkInQs = explode("?", trim($_POST['checkInQs'], "?"));
    $checkInSql = "INSERT INTO CheckListQs (ID, GeofenceID, type, question) VALUES ";
    $checkInInsertQuery = array();
    $checkInInsertData = array();
    foreach($checkInQs as $q){
         $checkInInsertQuery[] = "('',?, 1, ?)";
         $checkInData[] = $geofenceID;
         $checkInData[] = $q;
    }

基于另一个类似的示例，下面将介绍如何使用pdo完成它:

Based on another similar example, the following would be how to finish it off with pdo:

if (!empty($checkInInsertQuery)) {
        $checkInSql .= implode(', ', $checkInInsertQuery);
        $stmt = $db->prepare($checkInSql);
        $stmt->execute($checkInData);
    }

我不太确定如何绑定参数.我正在使用程序绑定.我通常会像这样绑定参数:

I'm not really sure how to bind the parameters in my case. I'm using procedural binding. I would usually bind parameters like so:

mysqli_stmt_bind_param($stmt, "is", $geofenceID, $question);
mysqli_stmt_execute($stmt);

我认为类型部分很简单:

I think the type part is as simple as:

$bindVar = '';

for ($i = 0; $i < count($checkInQs); $i++){
    $bindVar .= "is";
}

但是我不确定如何管理其余数据的传递吗?

But not I'm not sure how to manage passing in the rest of the data?

推荐答案

最后，我选择使用事务，提交和回滚来获得所需的结果:

In the end, I chose to make use of transactions, commits and rollbacks to get my desired outcome:

mysqli_query($con, "start transaction;");

$allQueriesOK = true;
$checkInQs = explode("?", trim($_POST['checkInQs'], "?"));
$checkInSql = "INSERT INTO CheckListQuestions (ID, GeofenceID, type, question) VALUES ('',?,0,?)";
mysqli_stmt_prepare($stmt, $checkInSql);

foreach ($checkInQs as $q) {            
    mysqli_stmt_bind_param($stmt, "is", $geofenceID, $q);
        if (!mysqli_stmt_execute($stmt)){
            $allQueriesOK = false;
            $message = mysqli_error($con);
            break;
        }
    }

    mysqli_stmt_close($stmt);

    if ($allQueriesOK){
        mysqli_query($con, "commit;");
    }
    else{
        mysqli_rollback($con);
    }       

这篇关于php绑定动态变量的数量，用于批量插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！