引用多个外键php mysql [英] referencing multiple foreign keys php mysql

问题描述

我对PHP / MySQL非常陌生，遇到了一些麻烦。帮助将不胜感激。

我有两个表格布局如下：
$ b

表队

team_id，team_name

表格

game_id，game_time，team1_id，team2_id，location
$ b

schedule.team1_id和schedule.team2_id都是team.team_id的外键。

我试图用team1_id和team2_id来引用team_name，但是我似乎只能得到team1的名字。这是我用过的查询不成功的。

  SELECT * FROM team 
 AS t JOIN schedule AS s 
 ON t.team_id = s.team1_id 
 WHERE位置='1'; 
  

我试图输出数据：

  while（mysql_fetch_assoc（$ result））{
 echo $ row ['team_name']; 
 
 
 
 $ b $ p 
 $ b 
这很明显，为什么这样做不起作用，只加入我需要的一列。帮帮我！我完全失去了如何解决这个问题。我相信有一个简单的解决方案，但我似乎无法找到它！
解决方案
想要查询的结果看起来像，但我假设你想获得有关两个参与每个游戏的团队的信息。为此，您必须加入团队表格两次： 
 
 
  SELECT t1.name，t2.name，s。* 
 FROM schedule AS 
 INNER JOIN team AS t1 ON t1.team_id = s.team1_id 
 INNER JOIN team AS t2 ON t2 .team_id = s.team2_id 
 WHERE s.location ='1'; 
  
 
I'm very new to php/MySQL and I'm having a bit of trouble.  Help would be much appreciated.


I have 2 tables laid out as such:

table team

team_id,team_name

table schedule

game_id,game_time,team1_id,team2_id,location

schedule.team1_id and schedule.team2_id are both foreign keys to team.team_id.

I'm trying to reference team_name using team1_id and team2_id but I can only ever seem to get the name for team1.  This is the query I've used unsuccessfully.
SELECT * FROM team
AS t JOIN schedule AS s 
ON t.team_id = s.team1_id 
WHERE location='1';
My attempt to output the data:
while (mysql_fetch_assoc($result)) {
    echo $row['team_name'];
}
It's quite obvious to me why this isn't working, as the tables are joined on only one of the columns I need.  Help!  I'm completely lost as to how to solve this problem.  I believe there is a simple solution, but I can't seem to find it!
解决方案
It's not quite clear on what you would like the results of your query to look like, but I am assuming you would like to get information about both teams involved in each game. To do so, you'll have to join the team table twice:
SELECT t1.name, t2.name, s.* 
FROM schedule AS s 
INNER JOIN team AS t1 ON t1.team_id = s.team1_id 
INNER JOIN team AS t2 ON t2.team_id = s.team2_id 
WHERE s.location='1';


                        
这篇关于引用多个外键php mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！