mercredi 22 juin 2016

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:

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