Page 1 of 1

Relational Database to HTML Table

Posted: Mon Feb 09, 2009 6:53 pm
by millsy007
I have 4 tables:
'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>&nbsp;</td>
                          </tr>
                          <tr>
                            <td>&nbsp;</td>
                          </tr>
                          <tr>
                            <td>&nbsp;</td>
                          </tr>
                          <tr>
                            <td>&nbsp;</td>
                          </tr>
                          <tr>
                            <td>&nbsp;</td>
                          </tr>
                        </table></td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top><br></td>
                        <td valign=top>&nbsp;</td>
                      </tr>
                      <tr>
                        <td><strong>Paris to Berlin</strong></td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                      </tr>
                      <tr>
                        <td><strong>Berlin to Prague</strong></td>
                        <td valign=top><br></td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                      </tr>
                      <tr>
                        <td><strong>Prague to Moscow</strong></td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                      </tr>
                      <tr>
                        <td><strong>Moscow to Bejing</strong></td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                        <td valign=top>&nbsp;</td>
                      </tr>
                    </table>";
 
echo $display_string;
 

Re: Relational Database to HTML Table

Posted: Mon Feb 09, 2009 9:25 pm
by t2birkey
What is the database structure look like for the table 'shuttle'? What is some sample data from the database look like? What is the value of $id from
millsy007 wrote:and journey.shuttle_id = '$id'
Assuming the MySQL query is correct as is, your problem is that you are assigning the person's name to a string variable. Every time the database returns a new name to the script you replace the string with the new persons name. You should place everyone's name into an array. A simple way to do that is to change $name = $row[passenger_name]; to read $name[] = $row[passenger_name]; This will store everyone's name into an array which you could parse through later.

After further reviewing this I saw a number of problems/suggestions I wish to make.. but first I have a few questions.. I am thinking a coach is like or is a train. Now I would think you would only have 1 coach stored in the database for the number of coaches you have. If I am right in thinking that.. I'm confused as to what thedepart_dttm feild is storing. Is it is a datetime type.. If it is I would assume you have 4 entries stored in the database for every 1 coach.. because you said you have 4 times a day they depart. If this is the case we need to come up with a new relationship between the train and when it leaves the station. Below will paste some code I wrote for you. It will pull the number of routes from the database and then query the database for each number of route to give you an array full of passenger names. This code is obviously not useful inthat you cannot just copy/paste it and make your code work. I hope you will be able to look at this code and get an id of so alternate solutions..

But once again, I want to help you get this issue resolved so please answer the above questions and we can help you get the best solution.

Code: Select all

 
$query = "SELECT count(id)
          FROM   routes";
//this will return the number of entries in the routes table
$qry_result = mysql_query($query) or die(mysql_error());
$qry_result = mysql_fetch_array($qry_result);
$routes = $qry_result['id'];
 
for($i=1; $i<=$routes; $i++){ //create a loop to go through all the routes stored in the database
    //I am not sure what your database looks like right now so I will assume your org. select statement was valid.
     $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 = '$i'";
     $qry_result = mysql_query($query) or die(mysql_error());
     while($row = mysql_fetch_array($qry_result)){
        $name[] = $row[passenger_name];
     }
}
 

Re: Relational Database to HTML Table

Posted: Tue Feb 10, 2009 4:17 am
by millsy007
Thanks for the help, I do not think I explained so well as this is confusing me, apologies, where it reads shuttle it should always say coach so:

Code: Select all

SELECT passengers.passenger_name
FROM     journey, passengers, coach
WHERE    coach.id = journey.coach_id
AND      journey.id = passengers.journey_id
AND      journey.coach_id = '$id'
AND      journey.route_id = '$i'
The Coach table has an id, and a depart_dttm. it is used as the main record from which there are a number of journeys. So a coach will always run from london to bejing 4 times a day but will have a number of journeys between (eg london to paris, paris to berlin), linked on coach.id = journey.coach_id, the route table is just a descriptive link of the route to the journey table (eg id=1 desc=london to paris)
Therefore some coach records could be:

id depart_dttm
1 30/02/2009 09:00
2 30/02/2009 12:00

from which a journey could have
id coach_id occupancy route_id
1 1 1 1

and passengers
id journey_id
1 1

In my table I would therefore like to show:
Each of the 4 coaches departing on that day. (9:00, 12:00, 16:00, 22:00)
The Journeys that make up these coaches (london to paris, paris to berlin etc)
Both of the above could be 'hard coded' as they are always the same, what I need to do is show for each journey a list of passengers who will be on the coach for that journey.

After looking at your code it seems I need to firstly loop through each coach departure for a day, then loop through each journey, then loop through the passengers on that journey?

Thanks again