Grouping database entries into dynamic HTML tables

I have a database where teams will have multiple entries each with different locations. Each entry will have a team name. So for example, team1 might appear several times but each time the location will be different.

The structure of the DB is (each of these represents a column header):

team_name, first_name, last_name, location, arrival_time

My current working code creates HTML tables grouped by team name but currently only creates one row to show the first location and the time of arrival for the first location. I need this to dynamically create more rows to show all locations and arrival times for each team.

The desired result would look like this – https://codepen.io/TheBigFolorn/pen/LqJeXr

But current result looks like this – https://codepen.io/TheBigFolorn/pen/qgMppx

And here is an example of how the DB table might look – https://codepen.io/TheBigFolorn/pen/daqJze

I’ve tried breaking up the echo and adding a second while loop before the row that I want to apply the above logic to but it seems to break everything. Any input on how I get this to work without having to use separate queries for each team would be very much appreciated. I’m new to php so please go easy on me 🙂

<?php

$leaders = "SELECT *, COUNT(location) FROM my_example_table  GROUP BY team_name";
$result = mysqli_query($connect, $leaders) or die ("<br>** Error in database table <b>".mysqli_error($connect)."</b> **<br>$sql");


if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
     echo "
        <div class='red-border'>
            <h2>". $row["team_name"]. "<br><small>Total locations visited: ". $row["COUNT(location)"]. "</small></h2>
        </div>
        <div class='data-holder'>
            <table>
                <tr>
                    <th>Location</th>
                    <th>Time of arrival</th>
                </tr>

                <tr><td>". $row["location"]. "</td> <td>". $row["arrival_time"]. "</td></tr>

            </table>
        </div>
        ";
    }
} else {
echo "0 results";
}

?>

Answer

Your problem is due to the GROUP BY, as you’ve probably realised. This is necessary in order to get a count per team, but causes the number of rows output to be only 1 per team – that’s what grouping does. Fundamentally, running an aggregate query such as a COUNT or SUM is incompatible with also outputting all of the row data at the same time. You either do one or the other.

Now, you could run two queries – one to get the counts, and one to get all the rows. But actually you don’t really need to. If you just select all the rows, then the count-per-team is implicit in your data. Since you’re going to need to loop through them all anyway to output them in the HTML, you might as well use that process to keep track of how many rows you’ve got per team as you go along, and create the “Total number of locations” headings in your HTML based on that.

Two things are key to this:

1) Making the query output the data in a useful order:

SELECT * FROM my_example_table  Order By team_name, arrival_time;

2) Not immediately echoing HTML to the page as soon as you get to a table row. Instead, put HTML snippets into variables which you can populate at different times in the process (since you won’t know the total locations per team until you’ve looped all the rows for that team), and then string them all together at a later point to get the final output:

$leaders = "SELECT * FROM my_example_table  Order By team_name, arrival_time;";
$result = mysqli_query($connect, $leaders) or die ("<br>** Error in database table <b>".mysqli_error($connect)."</b> **<br>$sql");

$currentTeam = "";
$locationCount = 0;
$html = "";
$teamHtmlStart = "";
$teamHtmlEnd = "";

if ($result->num_rows > 0)
{

while($row = $result->fetch_assoc()) 
{
  //run this bit if we've detected a new team
  if ($currentTeam != $row["team_name"]) { 
      //finalise the previous team's html and append it to the main output
      if ($currentTeam != "") $html .= $teamHtmlStart.$locationCount.$teamHtmlEnd."</table></div>";

      //reset all the team-specific variables
      $currentTeam = $row["team_name"];
      $teamHtmlStart = "<div class='red-border'><h2>".$currentTeam."<br><small>Total locations visited: ";
      $locationCount = 0;
      $teamHtmlEnd = "</small></h2>
        </div>
        <div class='data-holder'>
            <table>
                <tr>
                    <th>Location</th>
                    <th>Time of arrival</th>
                </tr>";
  }

  $teamHtmlEnd .= "<tr><td>". $row["location"]. "</td> <td>". $row["arrival_time"]. "</td></tr>";
  $locationCount++;
}

//for the final team (since the loop won't go back to the start):
$html .= $teamHtmlStart.$locationCount.$teamHtmlEnd."</table></div>";
echo $html;
}
else {
echo "0 results";
}

Here’s a runnable demo (using some static data in place of the SQL query): http://sandbox.onlinephpfunctions.com/code/2f52c1d7ec242f674eaca5619cc7b9325295c0d4