Multiple table query - display command name based on command id
My table structures
`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);
INTO INSERT 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 the team name ( Team1ID
, Team2ID
), say 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
";
source to share
Twice you can join the result of the lookup table with the command table so that you retrieve the match information and the name of each command. After that, you only need to combine the data that you are pulling from the 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 ;
}
source to share
Query:
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 code:
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";
}
source to share