php - 按级别对行进行分组并为每个行命名

Question

我有这张 table

id   ins  sec  div  subject  subjectHour  level
1     1    1    7   subName       5      level 1
2     1    2    7   subName2      4      level 1
3     1    2    7   subName3      4      level 2
4     1    1    7   subName4      6      level 2

我想输出类似的东西

一级科目
子名
子名2

二级科目
subName3
subName4

mysql JOIN 4 个表

    $displayMat = $db->prepare('SELECT
     a.id, a.ins_id, a.sub_name, a.subHour, a.level,
     b.id, b.ins_name,
     c.id, sec_name,
     d.id, div_name
     FROM ins_subjects AS a
     INNER JOIN institutes AS b
     INNER JOIN ins_sections AS c
     INNER JOIN ins_division AS d
     WHERE a.ins_id=?
     GROUP BY a.id;
 ');

这是循环

$displayMat->bind_param('i', $inId);
  if ($displayMat->execute()) {
  $results = $displayMat->get_result();
  while ($m = mysqli_fetch_array($results)) {
<li>
  <div class="col-xs-3"><?php print $m['sub_name']; ?></div>
  <div class="col-xs-3"><?php print $m['ins_name']; ?></div>
  <div class="col-xs-3"><?php print $m['sec_name']; ?></div>
  <div class="col-xs-3"><?php print $m['div_name']; ?></div>
</li>
}

Answer 1

您的 SQL 需要一些连接条件，而 GROUP BY 似乎是多余的。

从这样的事情开始:-

SELECT a.id, a.ins_id, a.sub_name, a.subHour, a.level,
        b.id, b.ins_name,
        c.id, sec_name,
        d.id, div_name
FROM ins_subjects AS a
INNER JOIN institutes AS b ON a.ins_id = b.id
INNER JOIN ins_sections AS c ON a.sec_id = c.id
INNER JOIN ins_division AS d ON a.div_id = d.id
ORDER BY a.level, a.sub_name

您可以依次阅读每一行。存储级别，当级别从上一行发生变化时，您输出级别。

编辑

在 php 中粗略地完成，像这样(这里没有使用列表)

<?php
$displayMat->bind_param('i', $inId);
if ($displayMat->execute()) 
{
    $results = $displayMat->get_result();
    $prev_level = '';
    while ($m = mysqli_fetch_array($results)) 
    {
        if ($prev_level != $m['level'])
        {
            echo $m['level'];
        }
        $prev_level = $m['level'];
        ?>
          <div class="col-xs-3"><?php print $m['sub_name']; ?></div>
          <div class="col-xs-3"><?php print $m['ins_name']; ?></div>
          <div class="col-xs-3"><?php print $m['sec_name']; ?></div>
          <div class="col-xs-3"><?php print $m['div_name']; ?></div>
        <?php
    }
}