Using SQL queries and php while loops to create an html table

Question

I have 5 SQL queries. I want to display the results of those in a table in HTML. Right now, I have nested while loops reading the results of those queries. However, the resulting table repeats the first row for the first 5 columns (Route appears in all the queries). The 6th column is correct.

Here is my table declaration, queries after connecting to the database, and while loops:

<table style="width:100%">
    <tr>
       <th>Route</th>
       <th>Total Cost ($)</th> 
       <th>Utilization (%)</th>
       <th>Number Locations Serviced</th>
       <th>Total Time (Hrs)</th>
       <th>Cost per Item Delivered ($)</th>
    </tr>

    $query1 = "SELECT RouteID as Route, ROUND(SUM(CostTo), 2) as TotalCost FROM VehicleRoutes, ContractID WHERE ContractID.ContractID >= ALL(SELECT ContractID.ContractID FROM ContractID) AND ContractID.ContractID = VehicleRoutes.ContractID GROUP BY RouteID";

    $result1 = mysqli_query($conn, $query1);

    $query2 = "SELECT Items.RouteID as Route, ROUND((COUNT(*)/ContractID.TruckCapacity)*100, 3) as UtilizationPercentage FROM Items, ContractID WHERE Items.ContractID = ContractID.ContractID AND Items.RouteID != 'NA' AND ContractID.ContractID >= ALL(SELECT ContractID.ContractID FROM ContractID) GROUP BY Items.RouteID";

    $result2 = mysqli_query($conn, $query2);

    $query3 = "SELECT V.RouteID as Route, COUNT(V.LocationIDTo) as NumLocationsServiced FROM VehicleRoutes as V, ContractID WHERE ContractID.ContractID >= ALL(SELECT ContractID.ContractID FROM ContractID) AND V.LocationIDTo != 'Warehouse' AND V.ContractID = ContractID.ContractID GROUP BY V.RouteID";

    $result3 = mysqli_query($conn, $query3);

    $query4 = "SELECT V.RouteID as Route, ROUND(SUM(V.TravelTime + V.DeliveryTime), 3) as TotalTime FROM VehicleRoutes as V, ContractID WHERE ContractID.ContractID >= ALL(SELECT ContractID.ContractID FROM ContractID) AND ContractID.ContractID = V.ContractID GROUP BY V.RouteID";

    $result4 = mysqli_query($conn, $query4);

    $query5 = "SELECT  I.RouteID AS Route, ROUND(SUM(V.CostTo) / COUNT(I.ItemID) , 2) AS CostPerItemDelivered FROM Items AS I, VehicleRoutes AS V, ContractID WHERE  ContractID.ContractID >= ALL(SELECT ContractID.ContractID FROM  ContractID) AND  I.RouteID !=  'NA' AND  I.RouteID =  V.RouteID AND  I.ContractID =  ContractID.ContractID AND  V.ContractID =  I.ContractID GROUP BY  I.RouteID";

    $result5 = mysqli_query($conn, $query5);

    while($col1 = mysqli_fetch_array($result1)){ 
        while($col2 = mysqli_fetch_array($result2)){
        while($col3 = mysqli_fetch_array($result3)){
            while($col4 = mysqli_fetch_array($result4)){
                while($col5 = mysqli_fetch_array($result5)){
                echo "<tr><td>".$col1["Route"]."</td><td>".$col1["TotalCost"]."</td><td>".$col2["UtilizationPercentage"]."</td><td>".$col3["NumLocationsServiced"]."</td><td>".$col4["TotalTime"]."</td><td>".$col5["CostPerItemDelivered"]."</td></tr>";
                    }
                }
            }
        }
    }

And here is the result I'm getting: I checked the results individually before putting them in the loops, so I know they are correct. I'm just not sure why the first row is repeating itself for everything but "Cost/Item Delivered."

Answer 1

You need to do is data mapping, to transform the source data (from SQL) to target data (table data). I have updated the while-loop part and this is one of the example how to transform the data:

$targetData = array();

function addData(&$targetData, $routeId, $dataKey, $dataValue) {
    if (!isset($targetData[$routeId])) $targetData[$routeId] = array();
    $targetData[$routeId][$dataKey] = $dataValue;
}

while($col1 = mysqli_fetch_array($result1)) {
    addData($targetData, $col1['Route'], 'TotalCost', $col1['TotalCost']);
}

while($col2 = mysqli_fetch_array($result2)) {
    addData($targetData, $col2['Route'], 'UtilizationPercentage', $col2['UtilizationPercentage']);
}

while($col3 = mysqli_fetch_array($result3)) {
    addData($targetData, $col3['Route'], 'NumLocationsServiced', $col3['NumLocationsServiced']);
}

while($col4 = mysqli_fetch_array($result4)) {
    addData($targetData, $col4['Route'], 'TotalTime', $col4['TotalTime']);
}

while($col5 = mysqli_fetch_array($result5)) {
    addData($targetData, $col5['Route'], 'CostPerItemDelivered', $col5['CostPerItemDelivered']);
}

foreach ($targetData as $routeId => $data) {

    echo '<tr>' . 
            '<td>' . $routeId . '</td>' .
            '<td>' . (isset($data['TotalCost']) ? $data['TotalCost'] : '&nbsp;') . '</td>' .
            '<td>' . (isset($data['UtilizationPercentage']) ? $data['UtilizationPercentage'] : '&nbsp;') . '</td>' .
            '<td>' . (isset($data['NumLocationsServiced']) ? $data['NumLocationsServiced'] : '&nbsp;') . '</td>' .
            '<td>' . (isset($data['TotalTime']) ? $data['TotalTime'] : '&nbsp;') . '</td>' .
            '<td>' . (isset($data['CostPerItemDelivered']) ? $data['CostPerItemDelivered'] : '&nbsp;') . '</td>' .
        '</tr>';

}