PHP MySQL complex joining

Question

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 and parlay games. Parlay Games references parlays table.

PARLAYS

id    userid    bettingsiteid    wager    odds
1     44        1                1000     2.0
2     45        5                1500     2.3
3     12        14               2000     1.8

PARLAY GAMES

id    parlayid    gameid    betinfo
1     1           1         Home Win
2     1           2         Away Win
3     1           5         Home Win
4     2           18        Home Win by 2 goals

This is what I would like to see in my final table:

Parlay ID    Home Team              Away Team              User           Wager    Odds 
             Team A                 Team B
1            Team C                 name of teams.id = 4   Ridge Robinson 1000     2.0
             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
        u.first_name AS 'User First Name',
        u.last_name AS 'User Last Name',
        u.id AS 'User ID',
        ht.name AS 'Home Team',
        away.name AS 'Away Team',
        p.wager AS 'Wager',
        p.odds AS 'Odds',
        pg.parlayid AS 'Parlay ID',
        pg.betinfo AS 'Bet Info',
        pg.gameid AS 'Game ID',
        g.date AS 'Game Date',
        b.betting_site_name AS 'Betting Site'
    FROM parlays p
    JOIN parlaygames pg ON p.id = pg.parlayid
    JOIN games g ON pg.gameid = g.id
    JOIN teams ht ON g.home_team = ht.id
    JOIN teams away ON g.away_team = away.id
    JOIN users u ON u.id = p.userid
    JOIN bonuses b ON p.bettingsiteid = b.id
    ORDER BY
        pg.parlayid,
        pg.gameid

And this is my table code:

<table class="table table-striped">
        <tr>
          <th>User Name</th>
          <th>Date</th>
          <th>Betting Site</th>
          <th>Home Team</th>
          <th>Away Team</th>
          <th>Wager</th>
          <th>Odds</th>
          <th>Manager</th>
          <th>Bet Info</th>
          <th>Success</th>
          <th>Edit</th>
        </tr>
<?php     

while($row = $result->fetch_array()) {
$hometeams = implode(' ',$row['Home Team']);

        $output = '<tr>';
    $output .= '<td><a href="userprofile.php?id='.$row['User ID'].'">'.$row['User First Name'].' '.$row['User Last Name'].'</a></td>';
    $output .= '<td>'.$row['Game Date'].'</td>';
    $output .= '<td>'.$row['Betting Site'].'</td>';
    $output .= '<td>'.$hometeams.'</td>';
    $output .= '<td>'.$row['Away Team'].'</td>';
    $output .= '<td>'.$row['Wager'].' kr</td>';
    $output .= '<td>'.$row['Odds'].'</td>';
    $output .= '<td><a href="managerprofile.php?id='.$row['Manager ID'].'">'.$row['Manager First Name'].' '.$row['Manager Last Name'].'</a></td>';
    $output .= '<td>'.$row['Bet Info'].'</td>';
    $output .= '<td>'.$row['Success'].'</td>';
    $output .= '<td><a href="edit_userbet.php?id='.$row['User ID'].'&betsite='.$row['Betting ID'].'&gameid='.$row['Game ID'].'&wager='.$row['Wager'].'&odds='.$row['Odds'].'&betinfo='.$row['Bet Info'].'" class="btn btn-default btn-sm">Edit</a></td>';
    $output .= '</tr>';

    echo $output;
}

?>
</table>

But this is very wrong as I am getting a table that shows (dumbed down)

Parlay ID    Home Team    Away Team    Wager    Odds
1            1            1            1000     2.0
1            2            2            1000     2.0
1            3            3            1000     2.0
-------------------------------------------------------------- (<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?

**EDIT #2 ** I have changed my DB table structure, but still need help displaying my front end table correctly!

Your table structure of Parlays is bad design. Having a comma separated list vs an actual table of a combination primary key of ( ParlayID, GameID) and have each distinct game as its own line with the corresponding parlay. THEN your query should work.. otherwise you have to join on the exploded values of each parlay ID. — DRapp
– DRapp, Commented Jun 22, 2016 at 17:20
DRapp...I can understand that this may be a bad design. I am certainly open to suggestions. Would you mind expanding a little more on your suggestion? I don't quite understand how I can set it up like that, because I am not sure which games will be in a parlay, or how many? Perhaps I am just not understanding the logic behind this? — Ridge Robinson
– Ridge Robinson, Commented Jun 22, 2016 at 17:27

DRapp · Accepted Answer · 2016-06-23 13:25:52Z

2

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...

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

ParlayGames
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.

select
      p.wager,
      p.odds,
      pg.parlayID,
      pg.gameID,
      ht.name as HomeTeam,
      away.name as AwayTeam
   from
      Parlay p
         join ParlayGames PG
            on p.ID = pg.parlayID
            join Games g
               on pg.GameID = g.ID
               join Teams ht
                  on g.home_team = ht.id
               join Teams away
                  on g.away_team = away.id
   order by
      Pg.ParlayID,
      pg.gameID

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

PER REVISIONS and additional details, the following should help pulling data. I created a SQLFiddle sample for you to look at the data, populated and queried to get all results. I even did a second query that pre-formats the return results with the and content. Might be overkill vs PHP concatenating, but just a sample of what you COULD do.

Anyhow, check out SQLFiddle, and below is the full copy of creating tables and sample data and final queries

CREATE TABLE Teams (  
  id int not null primary key,  
  name varchar(10)  );

insert into Teams 
  ( id, name )
  values
  ( 1, 'Team A' ),
  ( 2, 'Team B' ),
  ( 3, 'Team C' ),
  ( 4, 'Team 4' ),
  ( 12, 'Team 12' ),
  ( 16, 'Team 16' );

CREATE TABLE Games (
  id int not null primary key,  
  home_team int,
  away_team int,
  gameDate datetime,
  winning_team int );

INSERT INTO games 
  ( id, home_team, away_team, gameDate, winning_team )
  VALUES 
  (1,  1,  2, '2016-06-01',  1 ), 
  (2,  3,  4, '2016-06-02',  4 ),
  (3, 12, 16, '2016-06-03', 16 ),
  (18, 4, 12, '2016-06-09',  4 );

CREATE TABLE BettingSite (
  id int not null primary key,  
  betting_site_name varchar(10) );

insert into BettingSite 
  ( id, betting_site_name )
  values
  ( 1, 'Betting 1' ),
  ( 5, 'Betting 5' ),
  (14, 'Betting 14' );

CREATE TABLE Users (
  id int not null primary key,  
  first_name varchar(10),
  last_name varchar(10) );

insert into Users 
  ( id, first_name, last_name )
  values
  ( 12, 'First 12', 'Last 12' ),
  ( 44, 'F44', 'L44' ),
  ( 45, '45 User', '45 Last' );

CREATE TABLE Parlays (
  id int not null primary key,  
  userid int,
  bettingsiteid int,
  wager int,
  odds float(2) );

insert into Parlays 
  ( id, userid, bettingsiteid, wager, odds )
  values
  ( 1,  44,  1, 1000, 2.0 ),
  ( 2,  45,  5, 1500, 2.3 ),
  ( 3,  12, 14, 2000, 1.8 );

CREATE TABLE ParlayGames (
  id int not null primary key,  
  parlayid int, 
  gameid int,
  betinfo varchar(30) );

insert into ParlayGames 
  ( id, parlayid, gameid, betinfo )
  values
  ( 1, 1, 1, 'Home Win'),
  ( 2, 1, 2, 'Away Win'),
  ( 3, 1, 5, 'Home Win'),
  ( 4, 2, 18, 'Home by 2 goals' ),
  ( 5, 3, 2, 'Home Win' ),
  ( 6, 3, 18, 'Home Win' );


SELECT
      pg.ParlayID,
      pg.gameid,
      g.gameDate,
      g.winning_team,
      ht.name AS HomeTeam,
      away.name AS AwayTeam,
      u.first_name,
      u.last_name,
      u.id AS UserID,
      p.wager,
      p.odds,
      pg.betinfo,
      g.gamedate,
      bs.betting_site_name
   FROM 
      parlays p
         JOIN parlaygames pg 
            ON p.id = pg.parlayid
            JOIN games g 
               ON pg.gameid = g.id
               JOIN teams ht 
                  ON g.home_team = ht.id
               JOIN teams away 
                  ON g.away_team = away.id
         JOIN users u 
            ON p.userid = u.id
         JOIN BettingSite bs
            ON p.bettingsiteid = bs.id
   ORDER BY
      pg.parlayid,
      pg.gameid;


SELECT
      concat( '<tr><td><a href="userprofile.php?id=', u.id, '">', u.first_name, ' ', u.last_name, '</a></td>' ) as User,
      concat( '<td>', g.gameDate, '</td>' ) as GameDate,
      concat( '<td>', bs.betting_site_name, '</td>' ) as BettingSite,
      concat( '<td>', ht.name, '</td>' ) as HomeTeam,
      concat( '<td>', away.name, '</td>' ) as AwayTeam,
      concat( '<td>', p.wager, '</td>' ) as Wager,
      concat( '<td>', p.odds, '</td>' ) as Odds,
      concat( '<td><a href="managerprofile.php?id=', 123, '">', 'manager first name', ' ', 'manager last name', '</a></td>' ) as Manager,
      concat( '<td>', pg.betinfo, '</td>' ) as BetInfo,
      concat( '<td>', 'Was it successful', '</td>' ) as WasSuccess,
      concat( '<td><a href="edit_userbet.php?id=', u.id, 
             '&betsite=', bs.id, 
             '&gameid=', g.id,
             '&wager=', p.wager,
             '&odds=', p.odds,
             '&betinfo=', pg.betinfo, ' class="btn btn_default btn_sm">Edit</a></td>' ) as EditLink
   FROM 
      parlays p
         JOIN parlaygames pg 
            ON p.id = pg.parlayid
            JOIN games g 
               ON pg.gameid = g.id
               JOIN teams ht 
                  ON g.home_team = ht.id
               JOIN teams away 
                  ON g.away_team = away.id
         JOIN users u 
            ON p.userid = u.id
         JOIN BettingSite bs
            ON p.bettingsiteid = bs.id
   ORDER BY
      pg.parlayid,
      pg.gameid;

Some code I tried to pull together for you... slightly different output layout than you wanted. I did a two-row context. First, for each parlay, put the person and bet information. Then a next group of all bets within the parlay. Please NOTE.. I AM NOT a php developer, but thing this follows and should help understand what I am trying to do. I also know I do not have the "manager" information in my query table examples, and my column names are based on my query from SQLFiddle (I dont like having spaces within column names).

<table>

<?php

// hold values to identify break between each parlay
$lastParlay = 0;
// build string of header for each parlay
$curParlayBet = '';
// and another for each game WITHIN a parlay
$curParlayGames = '';

// start loop to fetch details
while($row = $result->fetch_array()) 
{
    // is the current record parlay ID the same as the previous record? 
    if( $lastParlay != $row['ParlayID'] )
    {
        $lastParlay = $row['ParlayID'];

        // if NOT equal, was there something prepared for the last parlay?
        if( strlen( $curParlayBet ) > 0 )
        {
            // yes, we had something prepared, dump it now.
            PrintOneParlay( $curParlayBet, $curParlayGames );
        }

        // at beginning of each new parlay, preserve the ID for next read cycle
        // and only need to prepare the holding string when parlay changes
        $curParlayBet = '<tr>'
            . ' <td><a href="userprofile.php?id=' 
                . $row['UserID'] . '">'
                . $row['UserFirstName'] .' '
                . $row['UserLastName'] 
                . '</a></td>'
            . '<td>' . $row['BettingSite'] . '</td>'
            . '<td>' . $row['Wager'] . '</td>'
            . '<td>' . $row['Odds'] . '</td>'
            . '<td><a href="managerprofile.php?id='
                . $row['ManagerID'] . '">'
                . $row['ManagerFirstName'] .' '
                . $row['ManagerLastName'] . '</a></td>'
            . '<td>' . $row['BetInfo'] . '</td>'
            . '<td>' . $row['Success'] . '</td>'
            . '</tr>';

        // always clear the rows of games as each parlay starts
        $curParlayGames = '';
        OneGame( $row );
    }

    // now, add rows for every game in each parlay.
    $curParlayGames .= OneGame( $row );

    echo $output;
}

// at completion of the loop, if anything pending, force a write
// based on the last record processed.
if( strlen( $curParlayGames ) > 0  )
    PrintOneParlay( $curParlayBet, $curParlayGames );



function OneGame( $curRow )
{
    RETURN '<tr>'
        . '<td />'
        . '<td>' . $curRow['GameDate'] .'</td>'
        . '<td>' . $curRow['HomeTeam'] .'</td>'
        . '<td>' . $curRow['AwayTeam'] .'</td>'
        . '<td colspan="3"><a href="edit_userbet.php?id='
            . $curRow['UserID']
            . '&betsite=' . $curRow['BettingID']
            . '&gameid=' . $curRow['GameID']
            . '&wager=' . $curRow['Wager']
            . '&odds=' . $curRow['Odds']
            . '&betinfo=' . $curRow['BetInfo']
            . '" class="btn btn-default btn-sm">Edit</a></td>'
        . '</tr>';
}

function PrintOneParlay( $parlayBet, $parlayGames )
{
    // first dump the per parlay header info
    echo '<tr>'
        . ' <td>User</td>'
        . ' <td>Betting Site</td>'
        . ' <td>Wager</td>'
        . ' <td>Odds</td>'
        . ' <td>Manager</td>'
        . ' <td>Bet Info</td>'
        . ' <td>Win/Lose</td>'
        . '</tr>';

    // now the data of who placed the bet info
    echo $parlayBet

    // now header info showing all games within the parlay
    echo  '<tr>'
        . ' <td />'
        . ' <td>Game Date</td>'
        . ' <td>Home Team</td>'
        . ' <td>Away Team</td>'
        . ' <td colspan="3">Edit</td>'
        . '</tr>';

    // finally all the games
    echo $parlayGames;  
}

?>

</table>

edited Jun 23, 2016 at 13:25

answered Jun 22, 2016 at 18:07

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Ridge Robinson Over a year ago

I see what you are saying...that makes a lot of sense. I am going to work towards implementing this code and come back with another comment/answer acceptance in a bit!

Ridge Robinson Over a year ago

DRapp...perhaps you might have a suggestion for this as well. I left off part of the table I have for my parlays table as I didn't find it necessary for the logic, but now I am unsure how to implement it. I have a single wager and single odds that are for a parlay, not for the individual games as part of the parlay. Do you know how I could implement these? Perhaps I need to add another table with that information?

DRapp Over a year ago

@RidgeRobinson, add the columns to the parlay table.

Ridge Robinson Over a year ago

@DRapp...the wager and odds are per PARLAY though, not per game in a parlay, if that makes sense. So putting extra columns doesn't seem to be of much use. Do you think it is too wet of code for me to add another table that is for Parlay Info? Such as date, wager, odds, user?

Ridge Robinson Over a year ago

@DRapp....Ok, so you agree that it makes sense to add another table with this info. Thanks for your continued input and help here

|

Collectives™ on Stack Overflow

PHP MySQL complex joining

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related