PHP和MYSQL组的结果来自数据库和显示

Question

当我想对数据库的结果进行分组时，我遇到了这个问题。我要表1)设备和我使用内部连接来获取相关数据。

$mysqli = new mysqli('localhost', 'name', 'pass', 'tbl');


$qry = "
        SELECT g.id as groupId, g.name as groupName, d.device_id
        FROM librenms.devices as d
        INNER JOIN librenms.device_groups as g
                ON d.hostname LIKE  CONCAT('%', mysql.SPLIT_STR(g.pattern, '\"', 2), '%')
        WHERE g.pattern LIKE '_devices.hostname%'
        ORDER BY g.id
";


$qid = $mysqli->query($qry); 

以下代码如下所示：

<table>
<tr>
    <th>Group Name</th>
    <th>Device ID</th>
</tr>
<?php while($row = $qid->fetch_assoc()){
    $deviceId = split(",",$row["device_id"]);
?>
    <tr>
        <td><?php echo $row["groupName"] ?> </td>
        <td><?php echo $deviceId[0]; array_shift($deviceId) ?></td>
    </tr>
    <?php foreach( $deviceId as $device){ ?>
        <tr>
            <td></td>
            <td><?php echo $device ?></td>
        </tr>
    <?php } ?>
<?php } ?>
</table>

我得到这样的结果：

Group Name  | Device ID
group1      | 154
group1      | 155
group1      | 156
group2      | 157
group2      | 158
group2      | 159
group3      | 160
group3      | 161

所以，我的问题是，用groupName或groupId分组日期并在下表中显示它的好方法是什么？正如您所看到的，我希望只打印一次组名，而属于这个组的所有设备都将与组名相关联地打印。

 Group Name     | Device ID
...........................
    group1      | 154
                | 155
                | 156
...........................
    group2      | 157
                | 158
                | 159
...........................
    group3      | 160
                | 161

Answer 1

可以通过在user variable中记录前一个组名来实现预期的结果。

更改$qry如下：

SELECT 
    g.id as groupId, 
    IF(g.name = @last_name, '', g.name) as groupName, 
    d.device_id, 
    @last_name := g.name
FROM librenms.devices as d
INNER JOIN librenms.device_groups as g
        ON d.hostname LIKE  CONCAT('%', mysql.SPLIT_STR(g.pattern, '\"', 2), '%')
CROSS JOIN (SELECT @last_name := '') param
WHERE g.pattern LIKE '_devices.hostname%'
ORDER BY g.id