Not sure exactly how to ask the question of what Im trying to do but will try explain it and insert the code.
But the basics of the question is Im running a sports website with a database with several tables.
PersonProfile
Coach_Career
League
Club
all linked via fields PersonProfile.pid to Coach_career.pid, Coach_career.league_id to League.league_id and coach_career.club_id to club.club.id.
What I was doing previously is doing a query in the coach_career table for any records that match the $pid via a url link to display a table showing the results with field from the coach_career table if the coach_career.league_id matched what was set in the query so to display the seperate league tables for that person I would have to duplicate the query a number of times but as the league_id is from another table wich is likely to be added to over time I would have to cintinually update the page adding extra code and queries every time. Is there a way I can code this to display them otherwise.
example is to group into a table for each league.
images attached.
<code>
$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);
}
$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 {
}
</code>