Ridge Robinson Ridge Robinson - 1 year ago 73
SQL Question

PHP MySQL complex joining

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:


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:


id name
1 Team A
2 Team B
3 Team C

Additionally, I have a parlays table


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:

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">
<th>Parlay ID</th>
<th>Home Team</th>
<th>Away Team</th>
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;


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?

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?

Answer Source

Your primary concept is ok, but the layout of the tables should be slightly altered for the Parlays. Change your Parlay table to something like...

REVISION per other items identified...

ID   Wager    Odds
1    $5       3:1
2    $2       2:1
3    $5       7:1

ID   ParlayID   GameID
1    1          1
2    1          2
3    1          3
4    2          2
5    2          9
6    3          12
6    3          3
6    3          18

Now, your query should be perfect to go.

      ht.name as HomeTeam,
      away.name as AwayTeam
      Parlay p
         join ParlayGames PG
            on p.ID = pg.parlayID
            join Games g
               on p.GameID = g.ID
               join Teams ht
                  on g.home_team = ht.id
               join Teams away
                  on g.away_team = away.id
   order by

Now, the parlay table can have as many games as you need... 2 or 200+

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download