从外键表中检索字段

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.

我想在这个PHP脚本中以json格式获取match表中每个团队的Team_Logo。我试图获得团队标识，但不幸的是查询无效。

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

3 个解决方案

#1

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.

还要注意，您不应该选择*，因为来自team表的Id列将覆盖matches表的Id列。这就是为什么应该只包含您想要选择的列。

#2

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

#3

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

#1

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