Interroga più tabelle: visualizza il nome della squadra in base all'ID team

Le mie strutture di tavoli sono

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

INSERIRE IN TblScore ( ScoreID , TeamID , MatchID , Score ) VALUES (1, 1, 1, 5), (2, 2, 1, 6), (3, 4, 2, 15), (4, 3, 2, 26);

Voglio mostrare Team Name di ( Team1ID , Team2ID ) dire il 19-11-2014 ci sono 4 partite quindi

 in PHP l'output dovrebbe essere

 Tempo: Tra:
 12:00:00 - 13:00:00 India v / s Pakistan
 13:10:00 - 14:10:00 Polonia v / s Brasile

 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_arrays($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 "; 

Puoi unire il risultato della tua tabella delle partite due volte con la tabella della tua squadra in modo da estrarre le informazioni della partita e il nome di each squadra. Dopo di che hai solo bisogno di concatenare i dati recuperati dal 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 

Codice PHP:

 while($row=mysqli_fetch_arrays($res)){ $mid= $row['MatchID']; $mdd = $row['MatchDate']; $t1 = $row['Team1ID']; $t2 = $row['Team2ID']; $t1n = $row['TeamName1']; $t2n = $row['TeamName2']; echo $t1n . " v/s . " $t2n ; } 

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 

codice php:

 while($row=mysqli_fetch_arrays($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"; }