Azure PHP Web App中MySQL数据按司机ID分组展示作业的实现问题

解决方案 #

你可以先将查询到的作业数据按司机ID分组，再遍历分组后的结果输出即可，修改后的完整代码如下：

function openJobsList()
{
    global $conn;
    $query = mysqli_query($conn, "SELECT openjobs.jobName, openjobs.jobType, openjobs.orderNumber, openjobs.referenceNumber, openjobs.pallets, openjobs.jobWeight, openjobs.jobStatus, driver.DriverID, driver.driverName
                                    FROM openjobs
                                    INNER JOIN driver ON openjobs.driver_fk = driver.DriverID
                                    ORDER BY driver.DriverID");

    // 按司机ID分组存储作业
    $driverJobs = [];
    while ($row = mysqli_fetch_assoc($query)) {
        $driverId = $row['DriverID'];
        if (!isset($driverJobs[$driverId])) {
            $driverJobs[$driverId] = [
                'driverName' => $row['driverName'],
                'jobs' => []
            ];
        }
        $driverJobs[$driverId]['jobs'][] = $row;
    }

    // 遍历分组后的数据输出
    foreach ($driverJobs as $driverId => $driverInfo) {
        $driverName = $driverInfo['driverName'];
        $jobs = $driverInfo['jobs'];
?>
        <div class='card mainPageJobCard my-2'>
            <div class='card-body'>
                <div class='row justify-content-between'>
                    <div class='col-11'>
                        <h5 class='card-title'>Driver: <?php echo $driverId; ?> - <?php echo $driverName; ?></h5>
                    </div>
                    <div class='col-1'>
                        <a href='pages/webAddJob.php' class='btn btn-primary btn-sm text-light rounded-pill'>Add Job</a>
                    </div>
                </div>
                <div class='row'>
                    <div class='col pt-3'>
                        <table class='table table-bordered table-responsive'>
                            <thead>
                                <tr class='table-light'>
                                    <th scope='col' class='col-2'>Job</th>
                                    <th scope='col'>Type</th>
                                    <th scope='col' class='col-2'>Order #</th>
                                    <th scope='col' class='col-2'>Reference</th>
                                    <th scope='col'>Pallets</th>
                                    <th scope='col'>Weight (kg)</th>
                                    <th scope='col' class='col-2'>Status</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($jobs as $job): ?>
                                <tr>
                                    <th><?php echo $job['jobName']; ?></th>
                                    <td><?php echo $job['jobType']; ?></td>
                                    <td><?php echo $job['orderNumber']; ?></td>
                                    <td><?php echo $job['referenceNumber']; ?></td>
                                    <td><?php echo $job['pallets']; ?></td>
                                    <td><?php echo $job['jobWeight']; ?></td>
                                    <td><?php echo $job['jobStatus']; ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
<?php
    }
}

逻辑说明 #

原来的代码每次遍历单条作业都会输出完整的卡片，所以每个作业对应独立卡片。修改后先把所有作业按司机ID做了分组，每个司机只生成一张卡片，卡片内的表格遍历该司机的所有作业生成行，就实现了同个司机的所有作业集中在同一个块内展示的效果。SQL中额外加了ORDER BY driver.DriverID保证输出的司机顺序一致，不需要可以删除。

内容的提问来源于stack exchange，提问作者Anya Webb