查询多个表 - 根据 Team ID 显示 Team Name [英] Query multiple tables - display Team Name based on Team ID
问题描述
我的表结构是
`TblTeam`(`TeamID`,`TeamName`)值(1,'印度'),(2,'巴基斯坦'),(3,'巴西')(4,'波兰');`TblMatch`(`MatchID`、`MatchDate`、`MatchStart`、`MatchEnd`、`Team1ID`、`Team2ID`)值(1, '19-11-2014', '12:00:00', '13:00:00', 1, 2),(2, '19-11-2014', '13:10:00', '14:10:00', 4, 3),(3, '19-11-2014', '14:20:00', '15:20:00', 1, 3),(4, '19-11-2014', '15:30:00', '16:30:00', 4, 2),(5, '20-11-2014', '10:00:00', '11:00:00', 1, 4),(6, '20-11-2014', '11:10:00', '12:10:00', 3, 4);
INSERT INTO TblScore
(ScoreID
, TeamID
, MatchID
, Score
)价值观(1, 1, 1, 5),(2, 2, 1, 6),(3, 4, 2, 15),(4, 3, 2, 26);
我想显示 (Team1ID
, Team2ID
) 的团队名称,例如 19-11-2014 有 4 场比赛,所以
SELECT m.MatchID, m.MatchDate, m.MatchStart, m.MatchEnd, m.Team1ID, m.Team2ID,t.TeamID, t.TeamNameFROM TblMatch m, TblTeam t WHERE m.MatchDate ='$todayis' ORDER BY m.MatchDate
PHP
while($row=mysqli_fetch_array($res)){$mid= $row['MatchID'];$mdd = $row['MatchDate'];$t1 = $row['Team1ID'];$t2 = $row['Team2ID'];$t1n = $row['TeamName'];回声 $t1n ."v/s ." $t1n ;}分数查询不起作用$query="选择m.MatchID,m.MatchDate,m.Team1ID,m.Team2ID,s.TeamID,s.MatchID,T1.TeamName 为 TeamName1,T2.TeamName 为 TeamName2,T1S.Score 作为 Team1Score,T2S.Score 作为 Team2Score从 TblMatch m JOIN TblTeam T1 ON m.Team1ID = T1.TeamID JOIN TblTeam T2 ON m.Team2ID = T2.TeamID JOIN TblScore s ON m.Team1ID = T1S.TeamID JOIN TblScore s ON m.Team1ID = T1S.在 m.Team2ID = T2S.TeamID 上,其中 s.MatchID=$mid";
您可以将您的比赛表结果与您的团队表连接两次,以便您提取比赛信息和每个团队的名称.之后你只需要在PHP中连接你从数据库中获取的数据.
SELECTm.MatchID,m.MatchDate,m.MatchStart,m.MatchEnd,m.Team1ID,m.Team2ID,T1.Teamname 为 Teamname1,T2.TeamName 为 Teamname2从 TblMatch M加入TblTeam T1M.TEAM1ID = T1.TeamID加入TblTeam T2M.TEAM2ID = T2.TeamID
PHP 代码:
while($row=mysqli_fetch_array($res)){$mid= $row['MatchID'];$mdd = $row['MatchDate'];$t1 = $row['Team1ID'];$t2 = $row['Team2ID'];$t1n = $row['TeamName1'];$t2n = $row['TeamName2'];回声 $t1n ."v/s ." $t2n ;}
My tables structures are
`TblTeam` (`TeamID`, `TeamName`) VALUES
(1,'India'),
(2,'Pakistan'),
(3,'Brazil')
(4,'Poland');
`TblMatch` (`MatchID`, `MatchDate`, `MatchStart`, `MatchEnd`, `Team1ID`, `Team2ID`) VALUES
(1, '19-11-2014', '12:00:00', '13:00:00', 1, 2),
(2, '19-11-2014', '13:10:00', '14:10:00', 4, 3),
(3, '19-11-2014', '14:20:00', '15:20:00', 1, 3),
(4, '19-11-2014', '15:30:00', '16:30:00', 4, 2),
(5, '20-11-2014', '10:00:00', '11:00:00', 1, 4),
(6, '20-11-2014', '11:10:00', '12:10:00', 3, 4);
INSERT INTO TblScore
(ScoreID
, TeamID
, MatchID
, Score
) VALUES
(1, 1, 1, 5),
(2, 2, 1, 6),
(3, 4, 2, 15),
(4, 3, 2, 26);
I want to display Team Name of (Team1ID
, Team2ID
) say on 19-11-2014 there are 4 matches so
in php the output should be Time : Between : 12:00:00 - 13:00:00 India v/s Pakistan 13:10:00 - 14:10:00 Poland v/s Brazil
SELECT m.MatchID, m.MatchDate, m.MatchStart, m.MatchEnd, m.Team1ID, m.Team2ID,
t.TeamID, t.TeamName
FROM TblMatch m, TblTeam t WHERE m.MatchDate ='$todayis' ORDER BY m.MatchDate
PHP
while($row=mysqli_fetch_array($res)){
$mid= $row['MatchID'];
$mdd = $row['MatchDate'];
$t1 = $row['Team1ID'];
$t2 = $row['Team2ID'];
$t1n = $row['TeamName'];
echo $t1n . " v/s . " $t1n ;
}
Score query does not work
$query="SELECT
m.MatchID,
m.MatchDate,
m.Team1ID,
m.Team2ID,
s.TeamID,
s.MatchID,
T1.TeamName as TeamName1,
T2.TeamName as TeamName2,
T1S.Score as Team1Score,
T2S.Score as Team2Score
FROM TblMatch m JOIN TblTeam T1 ON m.Team1ID = T1.TeamID JOIN TblTeam T2 ON m.Team2ID = T2.TeamID JOIN TblScore s ON m.Team1ID = T1S.TeamID JOIN TblScore s ON m.Team1ID = T1S.TeamID JOIN TblScore s ON m.Team2ID = T2S.TeamID WHERE s.MatchID=$mid
";
You can join your Match table result twice with your team table so you extract the information of the match and the name of each team. After that you only need concatenate the data you fetch from database in PHP.
SELECT
m.MatchID,
m.MatchDate,
m.MatchStart,
m.MatchEnd,
m.Team1ID,
m.Team2ID,
T1.Teamname as Teamname1,
T2.TeamName as Teamname2
FROM TblMatch M
JOIN TblTeam T1
ON M.TEAM1ID = T1.TeamID
JOIN TblTeam T2
ON M.TEAM2ID = T2.TeamID
PHP Code:
while($row=mysqli_fetch_array($res)){
$mid= $row['MatchID'];
$mdd = $row['MatchDate'];
$t1 = $row['Team1ID'];
$t2 = $row['Team2ID'];
$t1n = $row['TeamName1'];
$t2n = $row['TeamName2'];
echo $t1n . " v/s . " $t2n ;
}
这篇关于查询多个表 - 根据 Team ID 显示 Team Name的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!