查询多个表 - 根据 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 场比赛，所以

<前>在 php 中，输出应该是时间:之间:12:00:00 - 13:00:00 印度对巴基斯坦13:10:00 - 14:10:00 波兰对巴西

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屋！