Formatting Table Creation Function Output
Posted: Fri Feb 13, 2009 7:05 am
I have a function that will create a html table based on the query that is passed in:
This function works but I am trying (and failing) to change the format of the output of the table.
My query
Returns:
depart_dttm occupancy route_desc passengers
2009-02-10 09:00:00 3 hotel to airport
2009-02-10 09:00:00 3 airport to city center
2009-02-10 09:00:00 4 City Center to Downtown
2009-02-10 09:00:00 1 Downtown to Airport Tom
2009-02-10 09:00:00 0 Airport to Hotel
2009-02-10 12:00:00 0 hotel to airport
2009-02-10 12:00:00 0 airport to city center
2009-02-10 12:00:00 0 City Center to Downtown
2009-02-10 12:00:00 2 Downtown to Airport Bill|Mark
2009-02-10 12:00:00 0 Airport to Hotel
2009-02-10 16:00:00 0 hotel to airport
2009-02-10 16:00:00 0 airport to city center
2009-02-10 16:00:00 0 City Center to Downtown
2009-02-10 16:00:00 0 Downtown to Airport
2009-02-10 16:00:00 0 Airport to Hotel
2009-02-10 22:00:00 0 hotel to airport
2009-02-10 22:00:00 0 airport to city center
2009-02-10 22:00:00 0 City Center to Downtown
2009-02-10 22:00:00 0 Downtown to Airport
2009-02-10 22:00:00 0 Airport to Hotel
However I would like to change the format to be:
Because of the way the table is built using a loop in the SQLResultTable function is this possible?
Code: Select all
function SQLResultTable($Query)
{
$Table = ""; //initialize table variable
$Table.= "<table border='1' style=\"border-collapse: collapse;\">"; //Open HTML Table
$Result = mysql_query($Query); //Execute the query
if(mysql_error())
{
$Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>";
}
else
{
//Header Row with Field Names
$NumFields = mysql_num_fields($Result);
$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\">";
for ($i=0; $i < $NumFields; $i++)
{
$Table.= "<th>" . mysql_field_name($Result, $i) . "</th>";
}
$Table.= "</tr>";
//Loop thru results
$RowCt = 0; //Row Counter
while($Row = mysql_fetch_assoc($Result))
{
//Alternate colors for rows
if($RowCt++ % 2 == 0) $Style = "background-color: #00CCCC;";
else $Style = "background-color: #0099CC;";
$Table.= "<tr style=\"$Style\">";
//Loop thru each field
foreach($Row as $field => $value)
{
$Table.= "<td>$value</td>";
}
$Table.= "</tr>";
}
$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>";
}
$Table.= "</table>";
return $Table;
}My query
Code: Select all
$query = "SELECT tb1.depart_dttm, tb2.occupancy, tb3.route_desc, ";
$query .= "(SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) ";
$query .= "FROM passengers WHERE journey_id = tb2.id) AS passengers FROM coach AS tb1 LEFT JOIN journey AS tb2 ON ";
$query .= "( tb1.id = tb2.coach_id ) LEFT JOIN route AS tb3 ON ( tb2.route_id = tb3.id ) ";
$query .= "WHERE DATE(tb1.depart_dttm) = '2009-02-10' ORDER BY tb2.id ASC, tb2.route_id ASC;";depart_dttm occupancy route_desc passengers
2009-02-10 09:00:00 3 hotel to airport
2009-02-10 09:00:00 3 airport to city center
2009-02-10 09:00:00 4 City Center to Downtown
2009-02-10 09:00:00 1 Downtown to Airport Tom
2009-02-10 09:00:00 0 Airport to Hotel
2009-02-10 12:00:00 0 hotel to airport
2009-02-10 12:00:00 0 airport to city center
2009-02-10 12:00:00 0 City Center to Downtown
2009-02-10 12:00:00 2 Downtown to Airport Bill|Mark
2009-02-10 12:00:00 0 Airport to Hotel
2009-02-10 16:00:00 0 hotel to airport
2009-02-10 16:00:00 0 airport to city center
2009-02-10 16:00:00 0 City Center to Downtown
2009-02-10 16:00:00 0 Downtown to Airport
2009-02-10 16:00:00 0 Airport to Hotel
2009-02-10 22:00:00 0 hotel to airport
2009-02-10 22:00:00 0 airport to city center
2009-02-10 22:00:00 0 City Center to Downtown
2009-02-10 22:00:00 0 Downtown to Airport
2009-02-10 22:00:00 0 Airport to Hotel
However I would like to change the format to be:
Code: Select all
<table width="100%" border="1" cellspacing="0" cellpadding="0">
<tr>
<td width="20%"></td>
<td width="20%">9:00</td>
<td width="20%">12:00</td>
<td width="20%">16:00</td>
<td width="20%">22:00</td>
</tr>
<tr>
<td>hotel to airport </td>
<td>Bill | Mark</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>City Center to Downtown</td>
<td></td>
<td></td>
<td>Tom</td>
<td></td>
</tr>
<tr>
<td>Downtown to Airport</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Airport to Hotel</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>