PHP:无法将重新定义的数据保存到数据库
PHP:无法将重新定义的数据保存到数据库
提问于 2018-07-26 08:32:33
我尝试将数据插入到数据库中，方法是首先获取数据，执行一些加法操作，然后在循环中设置一个条件，如果值超过> 2200。在这个if条件中，我有一个for each循环，它将获取所有获取的记录并插入到第二个表中。到目前为止，我得到了正确的结果，现在的问题是从表中提取的剩余值没有插入到新表中。请找到附件中的截图(黄色单元格)。我还希望将它们保存并插入到两个表中，并为其赋值。
代码
if (isset($_POST["genRun"])) {
$total_weight = 0;
$i = 1;
$arr = array();
$excess = 0;
mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);
while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS)) {
$id = $row_FetchRecordRS['ID'];
$carr_ID = $row_FetchRecordRS['CarrierID'];
$address = $row_FetchRecordRS['DeliveryAddress'];
$potzone = $row_FetchRecordRS['Postzone'];
$instruction = $row_FetchRecordRS['DeliveryInstruction'];
$quantity = $row_FetchRecordRS['Quantity'];
$jobID = $row_FetchRecordRS['JobID'];
$jobName = $row_FetchRecordRS['JobName'];
$bundlesize = $row_FetchRecordRS['Bundlesize'];
$bundle = $row_FetchRecordRS['Bundles'];
$items = $row_FetchRecordRS['Items'];
$weight = $row_FetchRecordRS['WeightKgs'];
$suburb = $row_FetchRecordRS['Suburb'];
$num = $row_FetchRecordRS['TotalWeightKgs'];
//$num = $row_FetchRecordRS['TotalWeightKgs'];
$arr[] = array('CarrierID' => $carr_ID, 'DeliveryAddress' => $address, 'Postzone' => $potzone, 'DeliveryInstruction' => $instruction, 'Quantity' => $quantity, 'JobID' => $jobID, 'JobName' => $jobName, 'Bundlesize' => $bundlesize, 'Bundles' => $bundle, 'Items' => $items, 'WeightKgs' => $weight, 'Suburb' => $suburb, 'TotalWeightKgs' => $num);
if ($num + $total > 2200) {
$sqltransitlist = "INSERT INTO TransitList(genID, total) Values ('$i','$total')";
$ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
foreach ($arr as $data) {
$sqlquerytest = "INSERT INTO GenerateRun(CarrierID, DeliveryAddress, Postzone,DeliveryInstruction, Quantity, JobID, JobName,
Bundlesize, Bundles, Items, WeightKgs, Suburb, TotalWeightKgs,LodingZoneID) VALUES('"
. $data['CarrierID'] ."','" . $data['DeliveryAddress'] ."','" . $data['Postzone'] . "','" . $data['DeliveryInstruction']. "','" .$data['Quantity'] . "','" . $data['JobID'] . "','" . $data['JobName'] . "','" . $data['Bundlesize']. "','" .$data['Bundles'] . "','" . $data['Items'] . "','" .$data['WeightKgs']. "','" . $data['Suburb']."','" .$num."','" .$i ."')";
$ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB);
}
$arr = array();
$i++;
$total = 0;
} else {
$total += $num;
}
}
// after the loop check if there are some data was not inserted and insert it after the loop is over
if ($total > 0) {
$sqltransitlist = "INSERT INTO TransitList(genID, total) Values ('$i','$total')";
$ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
foreach ($arr as $data) {
$sqlquerytest = "INSERT INTO GenerateRun(CarrierID, DeliveryAddress, Postzone,DeliveryInstruction, Quantity, JobID, JobName,
Bundlesize, Bundles, Items, WeightKgs, Suburb, TotalWeightKgs,LodingZoneID) VALUES('"
. $data['CarrierID'] ."','" . $data['DeliveryAddress'] ."','" . $data['Postzone'] . "','" . $data['DeliveryInstruction']. "','" .$data['Quantity'] . "','" . $data['JobID'] . "','" . $data['JobName'] . "','" . $data['Bundlesize']. "','" .$data['Bundles'] . "','" . $data['Items'] . "','" .$data['WeightKgs']. "','" . $data['Suburb']."','" .$num."','" .$i ."')";
echo "$i , $total <br>";
$ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB);
}
}我现在得到的输出：(当我们将这些总数相加时，总数小于所需的输出)
所需的输出：(当我们做循环< 2200时，它不会保存超过2200的值)
SQL“，这就是sql：
SELECT UpdatedCsvFiles.ID, UpdatedCsvFiles.CarrierID, UpdatedCsvFiles.DeliveryAddress, UpdatedCsvFiles.Postzone, UpdatedCsvFiles.DeliveryInstruction, UpdatedCsvFiles.Quantity, UpdatedCsvFiles.JobID, UpdatedCsvFiles.JobName, UpdatedCsvFiles.Bundlesize, UpdatedCsvFiles.Bundles, UpdatedCsvFiles.Items, UpdatedCsvFiles.WeightKgs, SuburbPostZone.Suburb, UpdatedCsvFiles.TotalWeightKgs FROM UpdatedCsvFiles LEFT JOIN SuburbPostZone on SuburbPostZone.areaID = UpdatedCsvFiles.CarrierID Where UpdatedCsvFiles.DeliveryAddress != 'PLEASE LEAVE IN WAREHOUSE' GROUP by UpdatedCsvFiles.CarrierID, UpdatedCsvFiles.DeliveryAddress ORDER by UpdatedCsvFiles.CarrierID , SuburbPostZone.Suburb, UpdatedCsvFiles.ID ASC回答 1
Stack Overflow用户
回答已采纳
发布于 2018-07-26 09:36:58
下面是一段友好的伪代码：
// select db just once before the loop, you don't need to select db every time in the loop
mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);
// use while loop
while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS)) {
$id = $row_FetchRecordRS['ID'];
...
if ($num + $total > 2200) {
$sqltransitlist = "INSERT INTO TransitList(genID, total) ...";
$ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
foreach ($arr as $data) {
$sqlquerytest = "INSERT INTO GenerateRun(CarrierID ...";
$ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB);
}
$arr = array();
$i++;
$total = 0;
}
$total += $num;
$arr[] = array('CarrierID' => $carr_ID, 'DeliveryAddress'...);
}
// after the loop check if there are some data was not inserted and insert it after the loop is over
if ($total > 0) {
$sqltransitlist = "INSERT INTO TransitList(genID, total) ...";
$ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
foreach ($arr as $data) {
$sqlquerytest = "INSERT INTO GenerateRun(CarrierID ...";
$ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB);
}
}注意：您应该停止使用不推荐使用的mysql_*函数。并且您应该将预准备语句与mysqli或PDO函数一起使用，以避免sql注入
How can I prevent SQL injection in PHP?
聊天后的注释一些伪代码保存在这里：https://ideone.com/vRr5rA
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51529418
复制相关文章
相似问题