正确显示mySQL一对多查询的结果 [英] Properly display results of mySQL one-to-many query

问题描述

我有两个表:

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

和

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

TRIPS与LEGS有一对多的关系，因为每个tripID有多个legID.我需要以以下格式显示它们:

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...

我已经能够通过WHILE()循环遍历legID，但是在将LEGS循环嵌入TRIPS循环中时遇到了麻烦.我的查询是:

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:

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

推荐答案

1. 添加order by子句以按旅行ID排序
2. 创建$lastTripID变量以检查何时从新行程"中获得腿"
3. [推荐]使用join从多个表中选择数据

1. add order by clause to sort by trip ID
2. create $lastTripID variable to check when you get "legs" from "new trip"
3. [recommended] use join to select data from multiple tables

代码:

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

这篇关于正确显示mySQL一对多查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！