I am having difficulty in outputting the correct information from my SELECT query. These are tables I am pulling from.
I have a list of games:
GAMES
id home_team away_team
1 1 2
2 3 4
3 12 16
Where these home_team and away_team id's match up to:
TEAMS
id name
1 Team A
2 Team B
3 Team C
Additionally, I have a parlays table
PARLAYS
id gameids
1 1, 2, 3
2 2, 9
3 12, 3, 18
This is what I would like to see in my final table:
Parlay ID Home Team Away Team
Team A Team B
1 Team C name of teams.id = 4
name of teams.id = 12 name of teams.id = 16
-------------------------------------------------------------- (<tr> border)
Team C name of teams.id = 4
2 name of teams.id = x name of teams.id = x
-------------------------------------------------------------- (<tr> border)
I hope that makes sense. I just want the parlay id and then the name of each home and away team in the parlay.
This is my select query I am currently trying:
SELECT
p.gameids AS 'Game IDs',
p.id AS 'Parlay ID',
HomeTeam.name AS 'Home Team',
AwayTeam.name AS 'Away Team'
FROM parlays p
JOIN games g ON p.gameids = g.id
JOIN teams AS HomeTeam ON g.home_team = HomeTeam.id
JOIN teams AS AwayTeam ON g.away_team = AwayTeam.id
And this is my table code:
<table class="table table-striped">
<tr>
<th>Parlay ID</th>
<th>Home Team</th>
<th>Away Team</th>
</tr>
<?php
while($row = $result->fetch_array()) {
$gameid = str_replace(", ", "<br />", $row['Game IDs']);
$output = '<tr>';
$output .= '<td>'.$row['Parlay ID'].'</td>';
$output .= '<td>'.$gameid.'</td>';
$output .= '<td>'.$gameid.'</td>';
$output .= '</tr>';
echo $output;
}
?>
</table>
But this is very wrong as I am getting a table that shows:
Parlay ID Home Team Away Team
1 1
1 2 2
3 3
-------------------------------------------------------------- (<tr> border)
I know this is because I inserted $gameid, but I am unsure how to get the home and away team names per game id without referencing $gameid per row.
Does anybody have any suggestions?
EDIT One of the reasons that I am organizing the table like this is because, in my parlays table, in addition to just the parlay id and home team/away team...I also have one wager and one odds listed for each set. Perhaps someone has a better idea of a foreign key relationship that could work better and solve this problem that way?
Aucun commentaire:
Enregistrer un commentaire