Using the code below I want to group records by the league_id field and then display them in separate tables grouping by the league_id field and sort by season and use the league field in the table header.
Previously I used the same code and selected records by league_id field and displayed the tables which meant I had to add a new table code on the page if a new League was added to the database.
Is this possible at all and what would be the best way to do it.
<?php
$query = "Select coach_career.coach_career_id, coach_career.pid, coach_career.league_id, coach_career.club_id, coach_career.season, coach_career.pos, coach_career.games, coach_career.wins, coach_career.losses, coach_career.draws, coach_career.fingames, coach_career.finwins, coach_career.finlosses, coach_career.findraws, coach_career.gfgames, coach_career.gfwins, coach_career.gflosses, coach_career.gfdraws, coach_career.totgames, coach_career.totwins, coach_career.totlosses, coach_career.totdraws, coach_career.win_ratio, club.club, league.league From coach_career Inner Join club ON coach_career.club_id = club.club_id Inner Join league ON coach_career.league_id = league.league_id Where coach_career.pid = '$pid' Order By coach_career.season Asc, coach_career.league_id Asc";
$statement = $databaseConnection->prepare($query);
$statement->execute();
$statement->store_result();
$statement->bind_result($coach_career_id, $pid, $league_id, $club_id, $season, $pos, $games, $wins, $losses, $draws, $fingames, $finwins, $finlosses, $findraws, $gfgames, $gfwins, $gflosses, $gfdraws, $totgames, $totwins, $totlosses, $totdraws, $win_ratio, $club, $league);
if ($statement->error) {
die('Database query failed: ' . $statement->error);
}
// TODO: Check for == 1 instead of > 0 when Coach Career become unique.
$Exists = $statement->num_rows > 0;
if ($Exists) {
echo"<br /><table width=\"98%\">";
echo"<tr><th width=\"31%\" colspan=\"4\">Coaching Career</th><th width=\"16%\" colspan=\"4\"><center>Home & Away</center></th><th width=\"16%\" colspan=\"4\"><center>Finals</center></th><th width=\"16%\" colspan=\"4\"><center>Grand Finals</center></th><th width=\"16%\" colspan=\"4\"><center>Total Games</center></th><th colspan=\"2\"></th></tr>";
echo"<th class=\"category\" width=\"4%\"><center>Year</center></th><th class=\"category\" width=\"10%\">League</th><th class=\"category\" width=\"10%\">Club</th><th class=\"category\" width=\"3%\"><center>Pos</center></th><th class=\"category\" width=\"3%\"><center>Gms</center></th><th class=\"category\" width=\"3%\"><center>W</center></th><th class=\"category\" width=\"3%\"><center>L</center></th><th class=\"category\" width=\"3%\"><center>D</center></th><th class=\"category\" width=\"3%\"><center>Gms</center></th><th class=\"category\" width=\"3%\"><center>W</center></th><th class=\"category\" width=\"3%\"><center>L</center></th><th class=\"category\" width=\"3%\"><center>D</center></th><th class=\"category\" width=\"3%\"><center>Gms</center></th><th class=\"category\" width=\"3%\"><center>W</center></th><th class=\"category\" width=\"3%\"><center>L</center></th><th class=\"category\" width=\"3%\"><center>D</center></th><th class=\"category\" width=\"3%\"><center>Gms</center></th><th class=\"category\" width=\"3%\"><center>W</center></th><th class=\"category\" width=\"3%\"><center>L</center></th><th class=\"category\" width=\"3%\"><center>D</center></th><th class=\"category\"width=\"5%\"><center>Edit</center></th><th class=\"category\"width=\"5%\"><center>Del</center></th>";
$alternate = "1";
while ($statement->fetch()) {
if ($alternate == "1") {
$tbrow = "tbrow2";
$alternate = "2";
} else {
$tbrow = "tbrow1";
$alternate = "1";
}
echo"<tr><td width=\"4%\" class=\"$tbrow\"><center>$season</center></td><td width=\"10%\" class=\"$tbrow\">$league</td><td width=\"10%\" class=\"$tbrow\">$club</td><td width=\"3%\" class=\"$tbrow\"><center>$pos</center></td><td width=\"3%\" class=\"$tbrow\"><center>$games</center></td><td width=\"3%\" class=\"$tbrow\"><center>$wins</center></td><td width=\"3%\" class=\"$tbrow\"><center>$losses</center></td><td width=\"3%\" class=\"$tbrow\"><center>$draws</center></td><td width=\"3%\" class=\"$tbrow\"><center>$fingames</center></td><td width=\"3%\" class=\"$tbrow\"><center>$finwins</center></td><td width=\"3%\" class=\"$tbrow\"><center>$finlosses</center></td><td width=\"3%\" class=\"$tbrow\"><center>$findraws</center></td><td width=\"3%\" class=\"$tbrow\"><center>$gfgames</center></td><td width=\"3%\" class=\"$tbrow\"><center>$gfwins</center></td><td width=\"3%\" class=\"$tbrow\"><center>$gflosses</center></td><td width=\"3%\" class=\"$tbrow\"><center>$gfdraws</center></td><td width=\"3%\" class=\"$tbrow\"><center>$totgames</center></td><td width=\"3%\" class=\"$tbrow\"><center>$totwins</center></td><td width=\"3%\" class=\"$tbrow\"><center>$totlosses</center></td><td width=\"3%\" class=\"$tbrow\"><center>$totdraws</center></td><td width=\"5%\" class=\"$tbrow\"><center><a href='editcoachseason.php?ccid=$coach_career_id&lid=$lid&cid=$cid&pid=$pid'><img border=0 src=./Images/db_edit.png title=Edit></a></center></td><td width=\"5%\" class=\"$tbrow\"><center><a href='deletecoachseason.php?ccid=$coach_career_id&pid=$pid&alpha=$alpha&pn=$pn' onClick=\"return confirm('Are you sure?')\"><img border=0 src=./Images/db_delete.png title=Delete></a></center></td></tr>";
}
echo"</table>";
} else {
}
?>
