提问人：gopal 提问时间：9/23/2022 最后编辑：RiggsFollygopal 更新时间：9/23/2022 访问量：50
php ajax mysql 更新使用 for 循环需要更多时间来执行 有什么好的做法？
php ajax mysql update using for loop taking more time to execute what's good practice?
问：
在这里，我需要一次更新超过100个数据。这个查询对我有用，但它太慢了，至少需要 15 到 20 秒才能执行。 我尝试过一些东西，但失败了 任何帮助表示赞赏。iam 仍然学习。
<?php
include "../connection.php";
if (isset($_POST['close_val'])) {
$item_cid = $_POST["item_cid"];
$item_id = $_POST["item_id"];
$op_date = $_POST["op_date"];
$op_value = $_POST["op_value"];
$close_date = $_POST["close_date"];
$close_val = $_POST["close_val"];
$PurchaseRate = $_POST["PurchaseRate"];
$GeneralRate = $_POST["GeneralRate"];
$WholeSaleRate = $_POST["WholeSaleRate"];
$WholeSaleQty = $_POST["WholeSaleQty"];
for ($count = 0; $count < count($item_id); $count++) {
$item_cid_clean = mysqli_real_escape_string($conn, $item_cid[$count]);
$item_id_clean = mysqli_real_escape_string($conn, $item_id[$count]);
$op_date_clean = mysqli_real_escape_string($conn, $op_date[$count]);
$op_value_clean = mysqli_real_escape_string($conn, $op_value[$count]);
$close_date_clean = mysqli_real_escape_string($conn, $close_date[$count]);
$close_val_clean = mysqli_real_escape_string($conn, $close_val[$count]);
$PurchaseRate_clean = mysqli_real_escape_string($conn, $PurchaseRate[$count]);
$GeneralRate_clean = mysqli_real_escape_string($conn, $GeneralRate[$count]);
$WholeSaleRate__clean = mysqli_real_escape_string($conn, $WholeSaleRate[$count]);
$WholeSaleQty_clean = mysqli_real_escape_string($conn, $WholeSaleQty[$count]);
$updatequery = "UPDATE table1 SET
`item_cid` = '" . $item_cid_clean . "',
`item_id` = '" . $item_id_clean . "',
`op_date` = '" . $op_date_clean . "',
`op_value` = '" . $op_value_clean . "',
`close_date` = '" . $close_date_clean . "',
`close_val` = '" . $close_val_clean . "',
`PurchaseRate` = '" . $PurchaseRate_clean . "',
`GeneralRate` = '" . $GeneralRate_clean . "',
`WholeSaleRate` = '" . $WholeSaleRate__clean . "',
`WholeSaleQty` = '" . $WholeSaleQty_clean . "'
WHERE close_date='" . $close_date_clean . "'
and `item_id` = '" . $item_id_clean . "' ";
mysqli_query($conn, $updatequery);
}
$return_arr = array('item_cid' => $item_cid, 'item_id' => $item_id, 'op_date' => $op_date, 'bar' => $item_type);
echo json_encode($return_arr);
}
?>
这个ajax用于初始化Array以发布到php
var item_cid = [];
var item_id = [];
var op_date = [];
var op_value = [];
var close_date = [];
var close_val = [];
var PurchaseRate = [];
var GeneralRate = [];
var WholeSaleRate = [];
var WholeSaleQty = [];
// Initializing array with Checkbox checked values
$("input[name='item_cid[]']").each(function() {
item_cid.push(this.value);
});
$("input[name='item_id[]']").each(function() {
item_id.push(this.value);
});
$("input[name='op_date[]']").each(function() {
op_date.push(this.value);
});
$("input[name='op_value[]']").each(function() {
op_value.push(this.value);
});
$("input[name='close_date[]']").each(function() {
close_date.push(this.value);
});
$("input[name='close_val[]']").each(function() {
close_val.push(this.value);
});
$("input[name='PurchaseRate[]']").each(function() {
PurchaseRate.push(this.value);
});
$("input[name='GeneralRate[]']").each(function() {
GeneralRate.push(this.value);
});
$("input[name='WholeSaleRate[]']").each(function() {
WholeSaleRate.push(this.value);
});
$("input[name='WholeSaleQty[]']").each(function() {
WholeSaleQty.push(this.value);
});
$.ajax({
url: myurl,
type: 'post',
data: {
item_cid: item_cid,
item_id: item_id,
op_date: op_date,
op_value: op_value,
close_date: close_date,
close_val: close_val,
PurchaseRate: PurchaseRate,
GeneralRate: GeneralRate,
WholeSaleRate: WholeSaleRate,
WholeSaleQty: WholeSaleQty
},
dataType: 'JSON',
success: function(response) {
//success
}
在这里，php ajax html 代码的每一步都检查出来，纠正它的工作，只有 php 部分的 mysql 查询在循环中需要修复。
答：
1赞
RiggsFolly
9/23/2022
#1
如果在循环外部准备查询，则只会编译一次查询。如果您在循环中使用，则必须在每次更新时编译一次查询，这是您可以避免的开销，因为它完全是浪费时间。mysqli_query()
因此，将查询编写为准备好的参数化查询，如下所示，以便编译一次，然后使用它多次执行更新。这不会带来神奇的改进，但它应该是您始终进行数据库查询的方式，因为它还可以防止您的数据库免受 SQL 注入攻击
$sql = "UPDATE table1
SET `item_cid` = ?, `item_id` = ?, `op_date` = ?
`op_value` = ?, `close_date` = ?, `close_val` = ?,
`PurchaseRate` = ?, `GeneralRate` = ?,
`WholeSaleRate` = ?, `WholeSaleQty` = ?
WHERE close_date=? and `item_id` = ?";
$stmt = $conn->prepare($sql);
foreach( $_POST["item_id"] as $idx => $itemid ) {
// you may have to check the data types used in here
// however mysqli does not normally care about that as much as you might hope
$stmt->bind_param('ssssssssssss',
$_POST["item_cid"][$idx],
$itemid,
$_POST["op_date"][$idx],
$_POST["op_value"][$idx],
$_POST["close_date"][$idx],
$_POST["close_val"][$idx],
$_POST["PurchaseRate"][$idx],
$_POST["GeneralRate"][$idx],
$_POST["WholeSaleRate"][$idx],
$_POST["WholeSaleQty"][$idx],
$_POST["close_date"][$idx],
$_POST["item_id"][$idx]
);
$stmt->execute();
}
// not sure what you are trying to do here
// as these are arrays and you do this after the loop
$return_arr = array('item_cid' => $item_cid, 'item_id' => $item_id, 'op_date' => $op_date, 'bar' => $item_type);
echo json_encode($return_arr);
}
0赞
Rick James
9/23/2022
#2
如果您还没有这个，它应该有助于提高速度：
INDEX(item_id, close_date)
如需进一步讨论，请提供
SHOW CREATE TABLE- 构造后的示例查询。两个示例（如果生成大量单独的 UPDATE）。请确保item_id日期和关闭日期具有不同的值，除非其中一个值没有更改。
（您可以省略大部分列。
我也许可以向您展示如何在单个IODKU中完成所有更新。（但目前的要求是模糊的。
评论
MYSQLI_PDO'bar