如何避免PHP重复生成表格：实现每月单个统计表格

我需要实现每月生成一个统计表格，但当前代码会为单个月份的每条记录都生成一个表格，导致一个月出现3个表格。请帮忙修改代码，感谢！

原代码 #

function JobRaised(){
    include '../dbc.php';
    echo "<br><br>";
    echo "<div class='row'><div class='col-sm-6'><h3>Based on Origin</h3>";

    $queryt = "SELECT DISTINCT month, Category, Parcels, LoadParcels FROM mDraftProfileLM WHERE month > 202001 ORDER BY month DESC";
    $stmt = sqlsrv_query($conn, $queryt);
    if ($stmt === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    while ($row1 = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $month = $row1['month'];
        $category = $row1['Category'];
        $forecast = $row1['Parcels'];
        $actual = $row1['LoadParcels'];
        $percentage = $row1['Percentage'];

        // Start a new table
        echo "<table class='table table-bordered'>
            <thead>
                <tr class='info'>
                    <th style='background-color: light blue; color: black;' colspan='5'>" . $month . "</th>
                </tr>
                <tr>
                    <th></th>
                    <th>A</th>
                    <th>B</th>
                    <th>C</th>
                    <th>Total</th>
                </tr>
            </thead>
            <tbody>";

        // Add the row for forecast
        echo "<tr>
            <td>Forecast</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
        </tr>";

        // Add the row for actual
        echo "<tr>
            <td>Actual</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
        </tr>";

        // Add the row for percentage
        echo "<tr>
            <td>%</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
        </tr>";

        // Close the table
        echo "</tbody></table><br>";
    }

    echo "</div><div class='col-sm-6'></div></div>";
}

问题原因 #

原代码的核心问题是每循环一条数据库记录就生成一个完整表格，而同一个月份下存在A/B/C多个分类的记录，导致一个月生成和记录数等量的表格。

修改后的代码 #

function JobRaised(){
    include '../dbc.php';
    echo "<br><br>";
    echo "<div class='row'><div class='col-sm-6'><h3>基于起始地统计</h3>";

    // 修正SQL：添加Percentage字段，按月份+分类排序
    $queryt = "SELECT month, Category, Parcels, LoadParcels, Percentage FROM mDraftProfileLM WHERE month > 202001 ORDER BY month DESC, Category";
    $stmt = sqlsrv_query($conn, $queryt);
    if ($stmt === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    // 按月份分组存储所有分类数据
    $monthlyData = [];
    while ($row1 = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $month = $row1['month'];
        $category = $row1['Category'];
        
        $monthlyData[$month][$category] = [
            'forecast' => $row1['Parcels'],
            'actual' => $row1['LoadParcels'],
            'percentage' => $row1['Percentage'] ?? 0 // 处理字段为空的情况
        ];
    }

    // 遍历每个月份，生成单个表格
    foreach ($monthlyData as $month => $categories) {
        // 计算Total值（示例：各分类数值求和，可按需调整逻辑）
        $totalForecast = array_sum(array_column($categories, 'forecast'));
        $totalActual = array_sum(array_column($categories, 'actual'));
        $totalPercentage = $totalActual > 0 ? number_format(($totalForecast / $totalActual) * 100, 2) : 0;

        // 生成表格结构
        echo "<table class='table table-bordered'>
            <thead>
                <tr class='info'>
                    <th style='background-color: lightblue; color: black;' colspan='5'>" . $month . "</th>
                </tr>
                <tr>
                    <th></th>
                    <th>A</th>
                    <th>B</th>
                    <th>C</th>
                    <th>Total</th>
                </tr>
            </thead>
            <tbody>";

        // 预测值行
        echo "<tr>
            <td>预测值</td>
            <td>" . ($categories['A']['forecast'] ?? 0) . "</td>
            <td>" . ($categories['B']['forecast'] ?? 0) . "</td>
            <td>" . ($categories['C']['forecast'] ?? 0) . "</td>
            <td>" . $totalForecast . "</td>
        </tr>";

        // 实际值行
        echo "<tr>
            <td>实际值</td>
            <td>" . ($categories['A']['actual'] ?? 0) . "</td>
            <td>" . ($categories['B']['actual'] ?? 0) . "</td>
            <td>" . ($categories['C']['actual'] ?? 0) . "</td>
            <td>" . $totalActual . "</td>
        </tr>";

        // 百分比行
        echo "<tr>
            <td>%</td>
            <td>" . ($categories['A']['percentage'] ?? 0) . "</td>
            <td>" . ($categories['B']['percentage'] ?? 0) . "</td>
            <td>" . ($categories['C']['percentage'] ?? 0) . "</td>
            <td>" . $totalPercentage . "</td>
        </tr>";

        echo "</tbody></table><br>";
    }

    echo "</div><div class='col-sm-6'></div></div>";
}

补充说明 #

• 代码将英文表头改为中文，可按需改回原英文
• Total的计算逻辑为示例，可根据业务需求调整（比如取平均值或其他规则）
• 新增了字段为空的默认值处理，避免页面报错

内容的提问来源于stack exchange，提问作者Wathsala Heenkende