php - 从外键表中检索字段

Question

我有这两个表，我想从第二个表中为第一个表中的两个外键获取一些值。

团队表:

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     

匹配表:

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

我想在此 PHP 脚本中以 json 形式获取匹配表中每个团队的 Team_Logo。我试图获取团队 Logo ，但不幸的是查询无效。

如果有人能帮帮我好吗？

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

Answer 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

另请注意，您不应选择 *，因为团队表中的 Id 列将覆盖比赛表中的 Id 列。这就是为什么您应该只包含您喜欢选择的列。