php - MySQL 查询对结果进行分组

Question

我正在尝试执行多表查询并将结果包含到特定表中的记录。我确信这不是很清楚，所以这里是包含相关字段的表格:

waitingroom
    +----------------+-------------+
    | waitingroom_pk | waitingroom |
    +----------------+-------------+
    |         1      |   PATH2201  |
    +----------------+-------------+
    |         2      |   PATH2202  |
    +----------------+-------------+

waitingroom_case_lookup
    +----------------------------+----------------+---------+
    | waitingroom_case_lookup_pk | waitingroom_fk | case_fk |  
    +----------------------------+----------------+---------+
    |         1                  |   1            |  1      |  
    +----------------------------+----------------+---------+
    |         2                  |   1            |  2      |  
    +----------------------------+----------------+---------+
    |         3                  |   2            |  3      |  
    +----------------------------+----------------+---------+
vcase
    +---------+------------+------------+
    | case_pk | case_title | patient_fk |  
    +---------+------------+------------+
    |    1    |  Case One  |     1      |  
    +---------+------------+------------+
    |    2    |  Case Two  |     2      |  
    +---------+------------+------------+
    |    3    | Case Three |     3      |  
    +---------+------------+------------+

  patient
    +------------+------------+-----------+---------+
    | patient_pk | first_name | last_name | case_fk |
    +------------+------------+-----------+---------+
    | 1          | John       | Smith     |   1     |
    +------------+------------+-----------+---------+
    | 2          | Will       | Jones     |   2     |
    +------------+------------+-----------+---------+
    | 3          | Mary       | Ryan      |   3     |
    +------------+------------+-----------+---------+

我正在使用的查询是:

SELECT * FROM 
waitingroom, waitingroom_case_lookup, vcase, patient 
WHERE vcase.patient_fk = patient.patient_pk 
AND waitingroom.waitingroom_pk = waitingroom_case_lookup.waitingroom_fk 
AND vcase.case_pk = waitingroom_case_lookup.case_fk 
AND vcase.active = 'y' 
AND vcase.case_pk NOT IN (SELECT case_fk FROM user_case_lookup WHERE user_id = '$user_id')

此查询返回的结果显示三个等候室而不是两个，可能是因为表 waitingroom_case_lookup 中有 3 个记录。我想要的是使用 waitingroom_case_lookup 根据等候室对 vcase 数据进行分组。

至于打印结果，候诊室显示为 JQuery Accordion ，我有一个嵌套的 foreach 循环，它显示每个候诊室 Accordion 中的案例数据。问题是，正如我之前所说，显示的是三个候诊室，而不是两个。第二个和第三个候诊室 Accordion 显示正常，第二个 Accordion 中显示 case_pk 1 和 2，第三个 Accordion 中显示 case_pk 3。第一个 Accordion 也显示了 case_pk 一个，而这个 Accordion 不应该显示。

使用以下代码打印结果:

    <?php
$waitingRooms = array();

while ($row_waitingrooms = mysql_fetch_assoc($result_waitingrooms)){

        if($row_waitingrooms['gender'] == 'f'){
                $gender = 'Female';
            } else if ($row_waitingrooms['gender'] == 'm'){
                $gender = 'Male';
            }

        $waitingRoomPK = $row_waitingrooms['waitingroom_pk'];

        if (!isset($waitingRooms[$waitingRoomPK])) {
            $waitingRooms[$waitingRoomPK] = array(
                'waitingroom_pk' => $waitingRoomPK,
                'waitingroom' => $row_waitingrooms['waitingroom'],
                'cases' => array()
            );
        }

        $waitingRooms[$waitingRoomPK]['cases'][] = array(
            'case_pk' => $row_waitingrooms['case_pk'],
            'patient_icon' => $row_waitingrooms['patient_icon'],
            'first_name' => $row_waitingrooms['first_name'],
            'last_name' => $row_waitingrooms['last_name'],
            'age' => $row_waitingrooms['age'],
            'gender' => $gender,
            'presenting_complaint' => $row_waitingrooms['presenting_complaint']
        );



     echo "<h6>" . $row_waitingrooms['waitingroom'] . "</h6><div><p><span class='text'>";

    foreach ($waitingRooms[$waitingRoomPK]['cases'] as $wcase){
      echo "<table width='100%'><tr><td><input name='case' id='case_" . $wcase['case_pk'] . "' type='radio' value='" . $wcase['case_pk'] . "' /></td><td width='65' align='left'><div class='case_list'><img src='images/case_icons/" . $wcase['patient_icon'] . "'  width='44' height='45' /></div></td><td width='350'  align='left'>" . $wcase['first_name'] . ' ' . $wcase['last_name'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbspGender: ' . $wcase['gender'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Age: ' . $wcase['age'] . '<br />Presenting Complaint: ' . $wcase['presenting_complaint'] . "</td></tr></table>";
      }
    echo "</span></p></div>";
    }
    ?>

我怀疑需要更改查询以将案例数据 Accordion 分组到候诊室表，有什么想法我应该如何做到这一点？

编辑

感谢developerCK，我现在有了以下工作代码:

<?php
while ($row_waitingrooms = mysql_fetch_assoc($result_waitingrooms)){

    if($row_waitingrooms['gender'] == 'f'){
            $gender = 'Female';
        } else if ($row_waitingrooms['gender'] == 'm'){
            $gender = 'Male';
        }

    $waitingRoomPK = $row_waitingrooms['waitingroom_pk'];

    if (!isset($waitingRooms[$waitingRoomPK])) {
        $waitingRooms[$waitingRoomPK] = array(
            'waitingroom_pk' => $waitingRoomPK,
            'waitingroom' => $row_waitingrooms['waitingroom'],
            'cases' => array()
        );
    }

    $waitingRooms[$waitingRoomPK]['cases'][] = array(
        'case_pk' => $row_waitingrooms['case_pk'],
        'patient_icon' => $row_waitingrooms['patient_icon'],
        'first_name' => $row_waitingrooms['first_name'],
        'last_name' => $row_waitingrooms['last_name'],
        'age' => $row_waitingrooms['age'],
        'gender' => $gender,
        'presenting_complaint' => $row_waitingrooms['presenting_complaint']
    );

}

foreach($waitingRooms as $val){

echo "<h6>" . $val['waitingroom'] . "</h6><div><p><span class='text'>";

    foreach ($val['cases'] as $wcase){
      echo "<table width='100%'><tr><td><input name='case' id='case_" . $wcase['case_pk'] . "' type='radio' value='" . $wcase['case_pk'] . "' /></td><td width='65' align='left'><div class='case_list'><img src='images/case_icons/" . $wcase['patient_icon'] . "'  width='44' height='45' /></div></td><td width='350'  align='left'>" . $wcase['first_name'] . ' ' . $wcase['last_name'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbspGender: ' . $wcase['gender'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Age: ' . $wcase['age'] . '<br />Presenting Complaint: ' . $wcase['presenting_complaint'] . "</td></tr></table>";
      }
    echo "</span></p></div>";
}

?>

Answer 1

在 while 循环之后使用它，并删除 foreach 循环。你的查询是正确的。只需要更改一些php代码

foreach(waitingRooms as $val){

echo "<h6>" . $val['waitingroom'] . "</h6><div><p><span class='text'>";

    foreach ($val['cases'] as $wcase){
      echo "<table width='100%'><tr><td><input name='case' id='case_" . $wcase['case_pk'] . "' type='radio' value='" . $wcase['case_pk'] . "' /></td><td width='65' align='left'><div class='case_list'><img src='images/case_icons/" . $wcase['patient_icon'] . "'  width='44' height='45' /></div></td><td width='350'  align='left'>" . $wcase['first_name'] . ' ' . $wcase['last_name'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbspGender: ' . $wcase['gender'] . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Age: ' . $wcase['age'] . '<br />Presenting Complaint: ' . $wcase['presenting_complaint'] . "</td></tr></table>";
      }
    echo "</span></p></div>";
}