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屋!