I have these two tables and I want to get some values from the second table for two foreign key in the first table.
Team table:
ID | Name | No_Players | Country | F_Year | Team_Logo
-----+---------------+------------+----------+--------+----------------------
1 | Real Madrid | 22 | Spain | 1940 | Http://Xyz.jpg
2 | Arsenal | 22 | England | 1950 | Http://XXYYZZ.jpg
3 | FCB.Barcelona | 22 | Spain | 1960 | Http://YYZZz.jpg
4 | Bayern Meunekh| 22 | Germany | 1940 | Http://GGG.jpg
Matches table:
ID | Date |First_Team | Second_Team | M_Country |M_City | M_Period | Result
------------------------------------------------------------------------------------------
1 |15-02-2016 | Real Madrid | Arsenal | Spain | Madrid | 120 | 1-1
2 |19-03-2016 | FCB.Barecolna | Madrid | Spain | Madrid | 90 | 4-5
And I want to Get the Team_Logo For each team in the matches table in the json form in this PHP Script. I've tried to get teams logo but unfortunately the query is invalid.
If anyone can help me please?
<?php
require ('config.php');
$conn = mysqli_connect($servername, $username, $password, $db);
$query = "select * from matches,team where matches.first_team=team.Name OR matches.second_team=team.Name";
$result = mysqli_query($conn, $query);
$rows = array();
echo mysqli_error($conn);
while($row = mysqli_fetch_assoc($result)) {
$rows[]=$row;
}
echo json_encode($rows);
?>
SELECT
matches.*,
t1.Team_Logo AS logo1,
t2.Team_Logo AS logo2
FROM matches
JOIN team AS t1
ON t1.Name = matches.First_Team
JOIN team As t2
ON t2.Name = matches.Second_Team
Also note that you should not select * because the Id columns from the team tables will override the Id column of the matches table. That's why you should include only columns you like to select.
You should try this query :
$query="select m.*,t.* from matches as m INNER JOIN on team as t1 ON m.First_Team=t1.Name JOIN team As t2
ON m.Second_Team=t2.Name";
Use relational database structure.
add primary key for both table
add reference key of first table's primary key in second table as foreign key.
and refer bellow query.
sql="SELECT * FROM Matches as m JOIN Team as t ON t.ID = m.t_id where t.id=1 OR t.id=2