我很难从SELECT查询中输出正确的信息。这些是我要搬来的桌子。
我有一个游戏列表：
游戏
id home_team away_team
1 1 2
2 3 4
3 12 16
这些主队和客队的比赛地点：
团队
id name
1 Team A
2 Team B
3 Team C
另外，我还有一个parlays桌和parlay游戏。Parlay游戏引用parlays表。
帕雷斯
id userid bettingsiteid wager odds
1 44 1 1000 2.0
2 45 5 1500 2.3
3 12 14 2000 1.8
帕雷游戏
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
这是我想在最后一张桌子上看到的：
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)
我希望这有道理。我只需要parlay的id，然后是parlay中每个主客场球队的名字。
这是我当前正在尝试的选择查询：
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
这是我的表代码：
<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>
但这是非常错误的，因为我得到了一个表，显示（哑巴）
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)
我知道这是因为我插入了$game id，但是我不确定如何在不引用每行$gameid的情况下获得每个游戏id的主客队名称。
有人有什么建议吗？
编辑
我这样组织这张桌子的原因之一是，在我的parlays桌上，除了parlay id和主队/客队…我还有一个赌注和一个赔率为每套列出。也许有人对外键关系有更好的想法，这样可以更好地解决这个问题？
**编辑#2**
我已经更改了数据库表结构，但仍然需要帮助正确显示前端表！
最佳答案
您的主要概念是可以的，但是对于Parlays，表的布局应该稍微改变一下。把你的Parlay桌换成。。。
其他项目的修订。。。
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
现在，您的查询应该是完美的。
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
现在，parlay桌上可以有你需要的游戏。。。2或200+
根据修订和其他细节，以下内容应有助于提取数据。
我创建了一个SQLFiddle示例，供您查看数据、填充和查询以获得所有结果。我甚至做了第二个查询，用和内容预先格式化返回结果。可能是对PHP连接过度，但只是一个例子，你可以做什么。
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;
我试着为你拼凑一些代码。。。输出布局与您需要的稍有不同。我做了两行上下文。首先，对于每个parlay，输入person和bet信息。然后在parlay中进行下一组所有下注。请注意。。我不是一个php开发人员，但接下来的事情应该有助于理解我正在尝试做什么。我还知道我的查询表示例中没有“manager”信息，而且我的列名是基于我从SQLFiddle查询的（我不喜欢列名中有空格）。
<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>