查询多个表 - 根据团队 ID 显示团队名称 - Query multiple tables - display Team Name based on Team ID

Query multiple tables - display Team Name based on Team ID

本文关键字：团队 ID 显示查询 | 更新日期: 2023-09-27

我的表结构是

`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);

插入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 场比赛，所以

在 PHP 中，输出应该是时间 ： 之间 ：12：00：00 - 13：00：00 印度 v/s 巴基斯坦13：10：00 - 14：10：00 波兰 v/s 巴西

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
 ";

您可以将

比赛表结果与球队表联接两次，以便提取比赛信息和每个球队的名称。之后，您只需要连接从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代码：

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 ;
}

查询：

SELECT  
    m.MatchStart, 
    m.MatchEnd, 
    m.Team1ID, 
    m.Team2ID, 
    T1.TeamName as Teamname1, 
    T2.TeamName as Teamname2
FROM TblMatch m, TblTeam T1, TblTeam T2
 where  m.TEAM1ID = T1.TeamID
and
        m.TEAM2ID = T2.TeamID

PHP代码：

while($row=mysqli_fetch_array($res))
{
$mst= $row['MatchStart'];
$met = $row['MatchEnd'];
$t1n = $row['TeamName1']; 
$t2n = $row['TeamName2']; 
echo 'Time: 't't between:'r'n';
echo "$mst" . " - " . " $met 't't" ;
echo "$t1n vs $t2n 'r'n";
}