php - MySql Query 的分组输出

Question

我有以下 PHP/MySql：

$sql = "SELECT * from tblDigest";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

  echo "<table><tr>
    <th>Diagram</th>
    <th>Headcode</th>
    <th>Date</th>
    <th>Dep Time</th>
    <th>Origin</th>
    <th>Destination</th>
    <th>Arr Time</th>
    <th>Booked Traction</th>
    <th>Actual Traction</th>
    <th>Type</th>
    </tr>";

  // output data of each row
  while($row = $result->fetch_assoc()) {

    echo "<tr>";
    echo "<td>" . $row["diagram"] . "</td>";
    echo "<td>" . $row["headcode"] . "</td>";
    echo "<td>" . $row["depDate"] . "</td>";
    echo "<td>" . $row["depTime"] . "</td>";
    echo "<td>" . $row["origin"] . "</td>";
    echo "<td>" . $row["destination"] . "</td>";
    echo "<td>" . $row["arrTime"] . "</td>";
    echo "<td>" . $row["bookedTraction"] . "</td>";
    echo "<td>" . $row["actualTraction"] . "</td>";
    echo "<td>" . $row["type"] . "</td>";

    echo "</tr>";

  }

  echo "</table>";
}

我想按“图表”对结果进行分组，以便将它们显示为手风琴。有什么简单的方法可以做到这一点？

如果做不到这一点，我怎样才能获得表格第一行的值（这样我就可以有一个$diagNo变量并与之进行比较$row["diagram"]以找出它何时发生变化。

标签： phpmysql

Answer 1

对结果进行排序diagram并观察其值何时发生变化：

$sql = "SELECT * from tblDigest ORDER BY diagram";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

  echo "<table><tr>
    <th>Diagram</th>
    <th>Headcode</th>
    <th>Date</th>
    <th>Dep Time</th>
    <th>Origin</th>
    <th>Destination</th>
    <th>Arr Time</th>
    <th>Booked Traction</th>
    <th>Actual Traction</th>
    <th>Type</th>
    </tr>";

  // output data of each row
  $oldDiagram = '';
  while($row = $result->fetch_assoc()) {

    if($oldDiagram == '')
    {
      // this is the first diagram - create the first accordeon
      $oldDiagram = $row['diagram'];
    }
    elseif($oldDiagram != $row['diagram'])
    {
      // a new group starts right now - create new accordeon
      $oldDiagram = $row['diagram'];
    }
    echo "<tr>";
    echo "<td>" . $row["diagram"] . "</td>";
    echo "<td>" . $row["headcode"] . "</td>";
    echo "<td>" . $row["depDate"] . "</td>";
    echo "<td>" . $row["depTime"] . "</td>";
    echo "<td>" . $row["origin"] . "</td>";
    echo "<td>" . $row["destination"] . "</td>";
    echo "<td>" . $row["arrTime"] . "</td>";
    echo "<td>" . $row["bookedTraction"] . "</td>";
    echo "<td>" . $row["actualTraction"] . "</td>";
    echo "<td>" . $row["type"] . "</td>";

    echo "</tr>";

  }

  echo "</table>";
}