PHP（SQL）选择名称，其中id =更多值

Question

I have table match (which contains "id, date, team1_id, team2_id")

and table team (which contains "id, name")

I need PHP or SQL code that will show name of team(from table team) WHERE "team1_id" and team2_id" = team.id

...actually I have something like this:

$deleteMatch = Database::query('SELECT match.id, match.team1_id, match.date, 
       team.id, team.name FROM `match`, `team` WHERE team.id = match.team1_id');
foreach($deleteMatch as $matchinfo)
{
    ?><option value="<?php echo $matchinfo["id"];?>">
    <?php echo $matchinfo["date"]; echo $matchinfo["name"];?></option>
    <?php
}           

but I need add team2.id(name) to the foreach. ( DATE | TEAM1 NAME | TEAM2 NAME )

Hope you understood me. Thanks for help.

Answer 1

Use this Query:

SELECT match.id, match.team1_id, match.date, 
  t1.id as team1ID, t1.name as team1Name, 
   match.team2_id,t2.id as team2ID, t2.name as team2Name 
 FROM `match` JOIN `team` t1 ON t1.id = match.team1_id 
  JOIN `team` t2 ON t2.id = match.team2_id

SQLFiddle.