I have a table containing PRTL_BusRoute which refers to ID of various zones and AreaID as the ID's areas coming under each zone.
**ID** **BustrouteID** **AreaID**
0 1 3
1 1 5
1 1 6
2 2 5
3 2 6
4 2 8
5 3 9
6 3 12
7 4 1
8 4 8
9 4 11
The names of Bus routes are stored in tbl_BusRoute and areas are stored in dbo.GEN_Area.I need to display like this:
Zonename1
Areas coming under this zone in a list
Zonename2
Areas coming under this zone in a list
........
My php code is like this:
$area = '';
$getbus = mssql_query("SELECT BusRouteID,AreaID FROM dbo.Acc_BusRouteDetail");
while($data_getbus = mssql_fetch_row($getbus)){
$getZone = mssql_query("SELECT Busroute FROM PRTL_BusRoute
WHERE BusRouteID = '$data_getbus[0]'");
while($data_getZone = mssql_fetch_row($getZone)){
$zone_name = "<h4>".$data_getZone[0]."</h4>";
}
$getArea = mssql_query("SELECT AreaName_1 FROM dbo.GEN_Area
WHERE AreaID = '$data_getbus[1]' ORDER BY AreaName_1");
while($data_getArea = mssql_fetch_row($getArea)){
$area .= $data_getArea[0]."</br>";
}
echo $zone_name."<br/>".$area."<br/>";
}
The output is coming like this:
ZoneName1
Areaname1
ZoneName1
AreaName1
AreaName2
ZoneName1
AreaName1
Areaname2
AreaName3
ZoneName2
Areaname1
ZoneName2
Areaname1
Areaname2 so on
I would like to know if possible how can I select distinct busroutes and all the areas coming under it. I run out of logic how to select single route ID and then select all the areas coming under it
I'd change the SQL query a little to get all the data at once, and loop and print the area each time, while just printing the bus route if it changed since the last row. Something like this (untested) code;
$last_route = '';
$getbus = mssql_query("SELECT br.BusRoute,a.AreaName_1 FROM dbo.Acc_BusRouteDetail brd JOIN dbo.PRTL_BusRoute br ON br.busrouteid = brd.busrouteid JOIN dbo.GEN_Area a ON a.areaid = brd.areaid ORDER BY br.busroute, a.areaname_1");
while($data_getbus = mssql_fetch_array($getbus)){
if($last_route != $data_getbus['BusRoute']) {
$last_route = $data_getbus['BusRoute'];
echo '<h4>'.$last_route.'</h4>';
}
echo '<br/>'.$data_getbus['AreaName_1'].'</br>';
}