CODEWITHSUNDEEP
phpmysqlhtml-table
Stray Dog
Stray Dog

Reputation: 135

Display Table Rows Within Row

What I am trying to achieve best described in the example diagram below, in a html table. My table works fine with data from a MySQL database but all the data is printed in rows, which is correct, however I am required to display the data in the format below and the same format in Excel. This avoids repeating similar data.

My SQL statement is this: 

$query = mysql_query("SELECT * FROM Reservations WHERE Date = '$DateToday' ORDER BY Airline ASC, Flight ASC, Name");

but I guess to achieve the desired display php would be more relevant than MySQL.

Table Ecample So first the while loop....

while ($row = mysql_fetch_array($query)) { $results = mysql_query("SELECT *      FROM Reservations WHERE Airline = '$Airline'");
$Count = mysql_num_rows($results);
if($Count > 1) // More then 1 passenger on same flight
{
  $iReservationID     = $row['ReservationID'];
  $iAirline           = strtoupper($row['Airline']);
  $iFlight            = strtoupper($row['Flight']);
  $iName              = strtoupper($row['Name']);
  $iName              = "<BR>".$iName;                        
} else {// NOT More then 1 passenger on same flight
    $ReservationID    = $row['ReservationID'];
    $Airline          = strtoupper($row['Airline']);
    $Flight           = strtoupper($row['Flight']);
    $Name             = strtoupper($row['Name']);                     
  } 
}
<table>
  <tr>
    <td>AIRLINE</td>";
    <td>FLIGHT#</td>";
    <td>PASSENGER</td>";
  </tr>
  <tr>
    <td>echo $Airline;</td>
    <td>echo $Flight;</td>
    <td>print "$Name<BR>$iName</td>
  </tr>
</table>

The table works fine with rows for each entry when there is only one passenger but it is duplicating the passenger names when nested.

SELECT DISTINCT doesn't do the trick either nor SELECT FROM Reservations WHERE ReservationID <> $ReservationID - I have tried a second WHILE loop to refetch the data according to FLIGHT# but still not correct.

Suggestions greatly appreciated.

Upvotes: 0

Views: 376

Answers (2)

Vara Prasad
Vara Prasad

Reputation: 497

You can try this code

<?php
$query = mysql_query("SELECT *,GROUP_CONCAT(Name SEPARATOR '<BR>') AS PasName FROM Reservations WHERE Date = '$DateToday' GROUP BY Airline,Flight ORDER BY Airline ASC, Flight ASC, Name");
echo "<table>
<tr>
    <td>AIRLINE</td>
    <td>FLIGHT#</td>
    <td>PASSENGER</td>
</tr>";
while ($row = mysql_fetch_array($query)) {
    //$results = mysql_query("SELECT * FROM Reservations WHERE Airline = '$Airline'");

    $ReservationID = $row['ReservationID'];
    $Airline = strtoupper($row['Airline']);
    $Flight = strtoupper($row['Flight']);
    $Name = strtoupper($row['PasName']);

    //$Count = mysql_num_rows($results);
    /*if ($Count > 1) { // More then 1 passenger on same flight
        $iName = "";
        while ($inrow = mysql_fetch_array($results)) {
            $iName .= "<BR>".$inrow['Name'];
        }
    } else {// NOT More then 1 passenger on same flight
        // $ReservationID = $row['ReservationID'];
        // $Airline = strtoupper($row['Airline']);
        // $Flight = strtoupper($row['Flight']);
    }*/
    echo "<tr>
        <td>$Airline</td>
        <td>$Flight</td>
        <td>$Name</td>
    </tr>";
}
echo "</table>";
?>

But, I recommend you to fetch the multiple users within query only i.e replace this query 

SELECT * FROM Reservations WHERE Airline = '$Airline'

with

SELECT GROUP_CONCAT(Name SEPARATOR '<BR>') FROM Reservations WHERE Airline = '$Airline' GROUP BY Airline,Flight

so that there is no need of inside loop for concating user names

Upvotes: 1

Altair827
Altair827

Reputation: 369

Try this

$sql = "SELECT * FROM Reservations WHERE Date = '$DateToday' ORDER BY Airline ASC, Flight ASC, Name";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    echo "<table>
              <tr>
                <td>AIRLINE</td>
                <td>FLIGHT#</td>
                <td>PASSENGER</td>
              </tr>
          ";
    while($row = mysqli_fetch_assoc($result)) {
        echo "<tr>
                <td>"+$row['ReservationID']+"</td>
                <td>"+strtoupper($row['Airline'])+"</td>
                <td>"+strtoupper($row['Flight'])+"</td>
                <td>"+MethodToFetchNameOfPassengersOfTheFlight(params)+"</td>
            </tr>";

    echo "</table>";
}

for the method

string MethodToFetchNameOfPassengersOfTheFlight(params){
    //write a query to fetch the name of the passenger of the flight
    $CustomerNames = "";
    //in for each loop do this
    foreach($name in $names)
        $CustomerNames += $name + "<br>";
    return strtoupper($CustomerNames);
}

First we show the details of the airlines. Then we find the passengers of the corresponding flight and append them in a string with breaks and then distplay them.

Upvotes: 0

Related Questions