正常显示的MySQL结果一个一对多查询

Question

我有两个表：

 TRIPS 
----------------- 
tripID | clientID 

和

   LEGS 
-------------------------------- 
legID | depart | arrive | tripID 

TRIPS在与腿一个一对多的关系，有几个legID的每tripID。我需要在下面的格式显示出来：

Trip tripID1: 
    Leg legID1: depart1 - arrive1 
    Leg legID2: depart2 - arrive2 

Trip tripID2: 
    Leg legID3: depart3 - arrive3 
    Leg legID4: depart4 - arrive4 

etc... 

我已经能够通过通过WHILE()环路legIDs迭代，但我无法嵌入TRIPS环内腿循环。我的查询是：

<?php 
$legsQuery = "SELECT trips.tripID, legs.depart, legs.arrive FROM legs, trips WHERE `trips`.tripID = `legs`.tripID"; 
$legsQueryResult = mysql_query($legsQuery) or die("QUERY LEG ERROR: " . mysql_error()); 
while($row = mysql_fetch_assoc($legsQueryResult)) { 
    print_r($row); 
} 
?> 

Answer 1

1. 添加order by条款通过之旅ID进行排序
2. 创建$lastTripID变量，当你从 “新旅行” 获得 “腿”
3. [推荐]使用join检查从多个表中选择数据

代码：

<?php 
    $legsQuery = " 
     select 
      trips.tripID, 
      legs.depart, 
      legs.arrive 
     from 
      legs 
      inner join trips on trips.tripID = legs.tripID 
     order by 
      trips.tripID 
    "; 
    $legsQueryResult = mysql_query($legsQuery) or die("QUERY LEG ERROR: " . mysql_error()); 
    $lastTripID = null; 
    while ($row = mysql_fetch_assoc($legsQueryResult)) { 
     if ($row['tripID'] !== $lastTripID) { 
      echo $row['tripID'], "\n"; 
      $lastTripID = $row['tripID']; 
     } 
     print_r($row); 
    }