时间戳

首页 > 解决方案 > 如何获取记录以求和一个值，然后添加到两个新表中？

php - 如何获取记录以求和一个值，然后添加到两个新表中？

问题描述

我正在尝试从数据库中求和（添加值）。我的应用程序检查每一行的值，将每一行的值相加到 2000。一旦达到 2000，它就会保存在数据库中（插入查询）并继续相同，直到获取最后一条记录。每行合计（或总计）的总值不应超过 2000。

有两个插入查询，一个用于插入总数（从 1800 到 2000 之间的每一行）并生成 ID（如主键），第二个表添加插入的每一行 ID（生成的 ID 现在成为外键）

请参考截图。

在此处输入图像描述 请在下面找到代码：

$i = 1;
do {                
    $id = $row_FetchRecordRS['ID'];
    $dateissued = $row_FetchRecordRS['DateIssued'];
    $rundateCarrierRun = $row_FetchRecordRS['RundateCarrierRunID'];
    $timegenerated = $row_FetchRecordRS['TimeGenerated'];
    $carrierID = $row_FetchRecordRS['CarrierRunID'] ;
    $areaID = $row_FetchRecordRS['CarrierAreaID'];
    $address = $row_FetchRecordRS['DeliveryAddress'];
    $potzone = $row_FetchRecordRS['Postzone'];
    $carr_ID = $row_FetchRecordRS['CarrierID'];
    $instruction = $row_FetchRecordRS['DeliveryAddress'];
    $areaRep =  $row_FetchRecordRS['AreaRepDetails'];
    // $vendor = $row_FetchRecordRS['VendorDetails'];
    $quantity = $row_FetchRecordRS['Quantity'];
    $direct = $row_FetchRecordRS['Direct'];
    $jobID = $row_FetchRecordRS['JobID'];
    $jobName = $row_FetchRecordRS['JobName'];
    $bundlesize = $row_FetchRecordRS['Bundlesize'];
    $bundle = $row_FetchRecordRS['Bundles'];
    $items = $row_FetchRecordRS['Items'];
    $weight = $row_FetchRecordRS['WeightKgs'];
    $totalWeightCol = $row_FetchRecordRS['TotalWeightKgs'];
    $date = date("D M d, Y G:i");

    $total_weight =  $row_FetchRecordRS['FinalWeight'] + $total_weight ;
    echo "Row: " .$row_FetchRecordRS['FinalWeight']. "<br>";
    echo "Total is______  $i : $total_weight <br><br>";  

    $sqlquerytest = "INSERT INTO `GenerateRun`
        (`DateIssued`, `RundateCarrierRunID`, `TimeGenerated`, 
        `CarrierRunID`, `CarrierAreaID`, `DeliveryAddress`, `Postzone`, 
        `CarrierID`, `DeliveryInstruction`, `AreaRepDetails`,  
        `Quantity`, `Direct`, `JobID`, `JobName`, `Bundlesize`,   
        `Bundles`, `Items`, `WeightKgs`, `TotalWeightKgs`,   
        `LodingZoneID`) 
        VALUES 
        ('$dateissued', '$rundateCarrierRun', '$timegenerated',  
         '$carrierID', '$areaID', '$address', '$potzone', '$carr_ID',  
         '$instruction', '$areaRep', '$quantity', '$direct', '$jobID',  
         '$jobName', '$bundlesize', '$bundle', '$items', '$weight',   
        '$totalWeightCol','$i')";

    mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);
    $ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB) or die(mysql_error()); 

    if ($total_weight >= 1800) {            
        $sqltransitlist = " INSERT INTO `TransitList`(`genID`, `total`) Values ('$i','$total_weight')";
        mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);
        $ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB) or die(mysql_error());

        $i = $i+1;
        $total_weight = 0;          
    }
} while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS));

标签： phpmysqlsql

解决方案

您的某些代码行与您的问题无关。我会为你简化它。

$i = 1;
$total = 0;
$arr = array(); // for storing a list of data provides that the total doesn't exceed 2000
while ($row = mysql_fetch_assoc($record)) {
    $id = $row['id'];
    $name = $row['name'];
    $num = $row['num'];
    $arr[] = array('id' => $id, 'name' => $name, 'num' => $num);
    if ($num + $total > 2000) {
        $sql = "INSERT INTO Table1(genID, total) Values ('$i','$total')";
        mysql_query($sql) or die(mysql_error());
        foreach ($arr as $data) {
            $sql = "INSERT INTO Table2(ID, name, genID, total) Values ('$data[id]','$data[name]','$i','$data[num]')";
            mysql_query($sql) or die(mysql_error());
        }
        $arr = array(); // empty the array as the data has been stored to database
        $i++;
        $total = 0;   
    } else { // if the total doesn't exceed 2000, add it to total
        $total += $num;
    }
}
$sql = "INSERT INTO Table1(genID, total) Values ('$i','$total')";
mysql_query($sql) or die(mysql_error());
foreach ($arr as $data) {
    $sql = "INSERT INTO Table2(ID, name, genID, total) Values ('$data[id]','$data[name]','$i','$data[num]')";
    mysql_query($sql) or die(mysql_error());
}

注意：此代码只是一个示例，而不是您的实际代码。您可以实现我的代码并将其与您的代码相匹配。

推荐阅读