PHP while循环查询MySQL关联表工单未按司机分组如何解决？

安全阿岳

2026-10-2

问题描述

我有一个搭载MySQL数据库的Azure PHP Web App，需要访问数据库中的2张表，遍历每条条目并展示与每个ID关联的内容。

数据库结构

Driver表

driverID	driverName
(自增) 1	Bob

openJobs表

jobID	jobName	jobDate	destination	其他字段	driver_FK	driverName_FK
(自增) 1	job 1	9月1日周一	新西兰	blah blah	(从Driver表获取)	(从Driver表获取)

原有代码

数据库连接已在其他文件中引入，目前还未添加司机姓名的关联逻辑：

function openJobsList()
{
    $i = 4;

    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                                    
                                    WHERE driver.DriverID = $i");

    while ($row = mysqli_fetch_assoc($query)) {

        //$i++;

        $id = $row['DriverID'];
        //$driverName_fk = $row['driverName_fk'];
        $jobName = $row['jobName'];
        $jobType = $row['jobType'];
        $orderNumber = $row['orderNumber'];
        $referenceNumber = $row['referenceNumber'];
        $pallets = $row['pallets'];
        $jobWeight = $row['jobWeight'];
        $jobStatus = $row['jobStatus'];

        echo "<div class='card mainPageJobCard'>
                <div class='card-body'>
                    <div class='row justify-content-between'>
                        <div class='col-11'>
                            <h5 class='card-title'>Driver: {$id}</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>

                                <tr>
                                    <th>{$jobName}</th>
                                    <td>{$jobType}</td>
                                    <td>{$orderNumber}</td>
                                    <td>{$referenceNumber}</td>
                                    <td>{$pallets}</td>
                                    <td>{$jobWeight}</td>
                                    <td>{$jobStatus}</td>
                                </tr>                                 
                            </table>
                        </div>
                    </div>
                </div>
            </div>";
        //$i++;
        $id++;
    }
}

最初运行代码只能输出ID=4的条目（driverID从4开始自增），无法展示后续ID对应的内容。修复后新问题是：同一司机关联的所有工单会分开打印，而非全部归到对应司机的卡片下展示，是否需要新增一层while循环来打印工单？

解决方案

你现在的逻辑是每遍历一条工单就输出一整张司机卡片，自然同一个司机的多笔工单会生成多张卡片，不需要嵌套while循环，调整遍历逻辑、按司机分组输出即可：

先修改SQL，去掉WHERE限制（如果要展示所有司机），添加排序保证同个司机的工单连续返回：

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 ASC

调整PHP输出逻辑：记录上一个遍历的司机ID，只有司机ID变化时才输出新的卡片头部，同个司机的工单只输出表格行：

function openJobsList()
{
    global $conn;
    $sql = "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 ASC";
    $query = mysqli_query($conn, $sql);
    
    $prevDriverId = null; // 记录上一个遍历的司机ID
    
    while ($row = mysqli_fetch_assoc($query)) {
        $currentDriverId = $row['DriverID'];
        $driverName = $row['driverName'];
        // 提取工单字段
        $jobName = $row['jobName'];
        $jobType = $row['jobType'];
        $orderNumber = $row['orderNumber'];
        $referenceNumber = $row['referenceNumber'];
        $pallets = $row['pallets'];
        $jobWeight = $row['jobWeight'];
        $jobStatus = $row['jobStatus'];
        
        // 司机ID变化时，先关闭上一张卡片，再打开新卡片
        if ($currentDriverId !== $prevDriverId) {
            if ($prevDriverId !== null) {
                echo '
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>';
            }
            // 输出新司机的卡片头部
            echo "
            <div class='card mainPageJobCard'>
                <div class='card-body'>
                    <div class='row justify-content-between'>
                        <div class='col-11'>
                            <h5 class='card-title'>Driver: {$currentDriverId} - {$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>
            ";
            $prevDriverId = $currentDriverId;
        }
        
        // 仅输出当前工单的表格行
        echo "
                                    <tr>
                                        <th>{$jobName}</th>
                                        <td>{$jobType}</td>
                                        <td>{$orderNumber}</td>
                                        <td>{$referenceNumber}</td>
                                        <td>{$pallets}</td>
                                        <td>{$jobWeight}</td>
                                        <td>{$jobStatus}</td>
                                    </tr>
        ";
    }
    
    // 循环结束后关闭最后一张卡片
    if ($prevDriverId !== null) {
        echo '
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>';
    }
}

其他优化建议

尽量不要用global传递数据库连接，建议把$conn作为参数传入函数，避免全局变量污染
后续如果有动态查询条件，优先用参数化查询，避免SQL注入风险
如果需要展示没有工单的司机，把INNER JOIN改成LEFT JOIN，再加判断过滤空工单即可
统一数据库表字段的大小写规范，避免不同操作系统大小写敏感导致的查询报错

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

PHP while循环查询MySQL关联表 工单未按司机分组如何解决？

数据库结构 #

Driver表 #

openJobs表 #

原有代码 #