'coach' - id, depart_dttm (4 times a day at 9am, 12pm 4pm and 10pm)
'journey' - id, coach_id, route_id and occupancy
'passengers' - id, name, and journey_id
'route' - id and description (eg london to paris, 5 routes)
These tables show, for a particular coach departing on a certain date/time, the list of journeys and then which passengers are on them.
I am in the process of getting this information out of my database and into a html table.
I get the id of the current coach, I then want to use this to get out the journeys and passengers that make up this coach trip, so far I have the following, however this only returns one passenger on one journey. I want to be able to set variables for every journey/passenger and then fill the table with these values, is this a sensible approach? Am I on the right track with the code I have written?
Code: Select all
//For the first route
$route = 1;
while ( $route <= 5 ) {
$query = " select passengers.passenger_name
from journey, passengers, shuttle
where coach.id = journey.coach_id
and journey.id = passengers.journey_id
and journey.shuttle_id = '$id'
and journey.route_id = '$route'";
//Execute query
$qry_result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($qry_result)){
$name = $row[passenger_name];
}
$route += 1;
}html (table i would like to fill)
Code: Select all
$display_string = " <table width=100% border=1 cellspacing=2>
<tr>
<td width=32%><br></td>
<td width=17% valign=top><div align=center><strong>09:00</strong></div></td>
<td width=17% valign=top><div align=center><strong>12:00</strong></div></td>
<td width=17% valign=top><div align=center><strong>16:00</strong></div></td>
<td width=17% valign=top><div align=center><strong>22:00</strong></div></td>
</tr>
<tr>
<td><strong>London to Paris</strong></td>
<td valign=top><table width=100% border=1 cellspacing=0 cellpadding=0>
<tr>
<td>$name</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
</table></td>
<td valign=top> </td>
<td valign=top><br></td>
<td valign=top> </td>
</tr>
<tr>
<td><strong>Paris to Berlin</strong></td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
</tr>
<tr>
<td><strong>Berlin to Prague</strong></td>
<td valign=top><br></td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
</tr>
<tr>
<td><strong>Prague to Moscow</strong></td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
</tr>
<tr>
<td><strong>Moscow to Bejing</strong></td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
<td valign=top> </td>
</tr>
</table>";
echo $display_string;