Formatting Table Creation Function Output

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Formatting Table Creation Function Output

Post by millsy007 »

I have a function that will create a html table based on the query that is passed in:

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;
}
This function works but I am trying (and failing) to change the format of the output of the 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;";
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:

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>
Because of the way the table is built using a loop in the SQLResultTable function is this possible?
Post Reply