mysql 一对多查询_正确显示mySQL一对多查询的结果

I have two tables:

TRIPS

-----------------

tripID | clientID

and

LEGS

--------------------------------

legID | depart | arrive | tripID

TRIPS has a one-to-many relationship with LEGS in that there are several legID's per tripID. I need to display them in the following format:

Trip tripID1:

Leg legID1: depart1 - arrive1

Leg legID2: depart2 - arrive2

Trip tripID2:

Leg legID3: depart3 - arrive3

Leg legID4: depart4 - arrive4

etc...

I've been able to iterate through the legIDs via a WHILE() loop, but I'm having trouble embedding a LEGS loop inside the TRIPS loop. My query is:

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

}

?>

解决方案add order by clause to sort by trip ID

create $lastTripID variable to check when you get "legs" from "new trip"

[recommended] use join to select data from multiple tables

Code:

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

}