CODEWITHSUNDEEP
phpmysqlsqldatabaserelational-database
Dodo Dodo
Dodo Dodo

Reputation: 43

Retrieve Fields from Foreign key table

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

Upvotes: 0

Views: 110

Answers (3)

Nehal
Nehal

Reputation: 1523

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

Upvotes: 1

Hirendrasinh S. Rathod
Hirendrasinh S. Rathod

Reputation: 834

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

Upvotes: 0

Thomas Huijzer
Thomas Huijzer

Reputation: 364

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.

Upvotes: 3

Related Questions