Formatting Table Creation Function Output

[millsy007]

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

 

{
    $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

 

$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:

 

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?

 

[printf]

Just wrap the QUERY in (QUERY_IN HERE) the use a ORDER BY to get times to go in the order you want. I cannot remember your tables but I think this will work...

 

$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) ORDER BY route_id, coach_id;";

 

That will ordered it by COACH ID, THEN ROUTE_ID and the lastly REORDER it by the ROUTE_ID, but in the global scope, so actually we get the times set in the order you want. Note you cannot use table_names.column_name in the global scope, only a column_name, so lucky for you route_id was unique...

 

This is what the above query should return...

 

2009-02-10 09:00:00 3 hotel to airport
2009-02-10 12:00:00 0 hotel to airport
2009-02-10 16:00:00 0 Airport to Hotel
2009-02-10 22:00:00 0 airport to city center
2009-02-10 09:00:00 3 airport to city center
2009-02-10 12:00:00 0 airport to city center
2009-02-10 16:00:00 0 airport to city center
2009-02-10 22:00:00 0 hotel to airport
2009-02-10 09:00:00 4 City Center to Downtown
2009-02-10 12:00:00 0 City Center to Downtown
2009-02-10 16:00:00 0 hotel to airport
2009-02-10 22:00:00 0 City Center to Downtown
2009-02-10 09:00:00 1 Downtown to Airport Tom
2009-02-10 12:00:00 2 Downtown to Airport Bill|Mark
2009-02-10 16:00:00 0 City Center to Downtown
2009-02-10 22:00:00 0 Downtown to Airport
2009-02-10 09:00:00 0 Airport to Hotel
2009-02-10 12:00:00 0 Airport to Hotel
2009-02-10 16:00:00 0 Downtown to Airport
2009-02-10 22:00:00 0 Airport to Hotel

 

You should be able to make your display without much effort now!

[millsy007]

Thanks for your help (again) unfortunately I am getting the error:

MySQL ERROR: Unknown column 'route_id' in 'order clause'

When I try adding the tb2 prefix to the order clause I get:

MySQL ERROR: Table 'tb2' from one of the SELECTs cannot be used in global ORDER clause

 

 

[millsy007]

Apologies, you mentioned the reason that using the table prefix wouldnt work. However I am unsure at to why I get Unknown column 'route_id' in 'order clause' This is unique and definitaly in the journey table?