How can I Group and Display MySQL Results by Field Data in an HTML Table?

Grouping MySQL Results by Field Data

Initial Query

The provided MySQL database contains information about groups and names. The goal is to display the data in an HTML table organized by group, with each group displaying the names associated with it. To achieve this, a simple SQL query can be used:

<code class="sql">SELECT
  Group,
  GROUP_CONCAT(Name) AS Names
FROM
  database_table
GROUP BY
  Group;</code>

PHP Implementation

Once the results are retrieved, they can be iterated through using PHP to generate the HTML table. The following code snippet illustrates how this can be done:

<code class="php">$result = $mysqli->query($sql);
echo "<table>";
echo "<tr><th>Group</th><th>Name</th></tr>";
while ($row = $result->fetch_assoc()) {
  $names = explode(",", $row['Names']);
  echo "<tr><td>{$row['Group']}</td><td>{$names[0]}</td></tr>";
  array_shift($names);
  foreach ($names as $name) {
    echo "<tr><td></td><td>$name</td></tr>";
  }
}
echo "</table>";</code>

Complex Grouping with Multiple Tables

The second part of the question introduces a more complex scenario involving multiple tables with different fields. To handle this, the query can be modified to join the tables and include the additional fields:

<code class="sql">SELECT
  p.Group,</code>

The above is the detailed content of How can I Group and Display MySQL Results by Field Data in an HTML Table?. For more information, please follow other related articles on the PHP Chinese website!