Relational Database Records into a HTML Table

millsy007 · February 10, 2009

I have 4 tables, 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.

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.

So far I have

$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];   
     }

But obviously this is only bringing out one passenger of one journey for one route. 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?

sasa · February 10, 2009

is it homework?

millsy007 · February 10, 2009

No it is a project I am doing as a favour for someone who runs an airport shuttle, thought it would be a nice straightforward one to learn some php but I am well and truly stuck

I need a plan of a logical way to loop through each of the records?

printf · February 10, 2009

show me your create table schemes because I forgot how you had them setup, once you do that I will give query to get the results you want.

millsy007 · February 10, 2009

Hi these are my tables

CREATE TABLE IF NOT EXISTS `journey` (

`id` int(11) NOT NULL auto_increment,

`coach_id` int(11) NOT NULL,

`route_id` int(11) default NULL,

`occupancy` int(11) default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6721 ;

CREATE TABLE IF NOT EXISTS `coach` (

`id` int(11) NOT NULL auto_increment,

`depart_dttm` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1345 ;

CREATE TABLE IF NOT EXISTS `route` (

`id` int(11) NOT NULL auto_increment,

`route_desc` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

CREATE TABLE IF NOT EXISTS `passengers` (

`id` int(11) NOT NULL auto_increment,

`journey_id` int(11) NOT NULL,

`passenger_name` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Thanks for your help

printf · February 10, 2009

Can you show me (1) day of your data or tell if your data for one day looks like this...

-- 
-- Table structure for table `coach`
-- 

CREATE TABLE `coach` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `depart_dttm` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `coach`
-- 

INSERT INTO `coach` VALUES (1, '2009-02-10 09:00:00');
INSERT INTO `coach` VALUES (2, '2009-02-10 12:00:00');
INSERT INTO `coach` VALUES (3, '2009-02-10 16:00:00');
INSERT INTO `coach` VALUES (4, '2009-02-10 22:00:00');

-- --------------------------------------------------------

-- 
-- Table structure for table `journey`
-- 

CREATE TABLE `journey` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coach_id` int(11) NOT NULL,
  `route_id` int(11) DEFAULT NULL,
  `occupancy` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;

-- 
-- Dumping data for table `journey`
-- 

INSERT INTO `journey` VALUES (1, 1, 1, 0);
INSERT INTO `journey` VALUES (2, 1, 2, 0);
INSERT INTO `journey` VALUES (3, 1, 3, 0);
INSERT INTO `journey` VALUES (4, 1, 4, 1);
INSERT INTO `journey` VALUES (5, 1, 5, 0);
INSERT INTO `journey` VALUES (6, 2, 1, 0);
INSERT INTO `journey` VALUES (7, 2, 2, 0);
INSERT INTO `journey` VALUES (8, 2, 3, 0);
INSERT INTO `journey` VALUES (9, 2, 4, 2);
INSERT INTO `journey` VALUES (10, 2, 5, 0);
INSERT INTO `journey` VALUES (11, 3, 1, 0);
INSERT INTO `journey` VALUES (12, 3, 2, 0);
INSERT INTO `journey` VALUES (13, 3, 3, 0);
INSERT INTO `journey` VALUES (14, 3, 4, 0);
INSERT INTO `journey` VALUES (15, 3, 5, 0);
INSERT INTO `journey` VALUES (16, 4, 1, 0);
INSERT INTO `journey` VALUES (17, 4, 2, 0);
INSERT INTO `journey` VALUES (18, 4, 3, 0);
INSERT INTO `journey` VALUES (19, 4, 4, 0);
INSERT INTO `journey` VALUES (20, 4, 5, 0);
INSERT INTO `journey` VALUES (21, 5, 1, 0);
INSERT INTO `journey` VALUES (22, 5, 2, 0);
INSERT INTO `journey` VALUES (23, 5, 3, 0);
INSERT INTO `journey` VALUES (24, 5, 4, 0);
INSERT INTO `journey` VALUES (25, 5, 5, 0);

-- --------------------------------------------------------

-- 
-- Table structure for table `passengers`
-- 

CREATE TABLE `passengers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `journey_id` int(11) NOT NULL,
  `passenger_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `passengers`
-- 

INSERT INTO `passengers` VALUES (1, 4, 'Tom');
INSERT INTO `passengers` VALUES (2, 9, 'Bill');
INSERT INTO `passengers` VALUES (3, 9, 'Mark');

-- --------------------------------------------------------

-- 
-- Table structure for table `route`
-- 

CREATE TABLE `route` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `route_desc` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `route`
-- 

INSERT INTO `route` VALUES (1, 'hotel to airport');
INSERT INTO `route` VALUES (2, 'airport to city center');
INSERT INTO `route` VALUES (3, 'City Center to Downtown');
INSERT INTO `route` VALUES (4, 'Downtown to Airport');
INSERT INTO `route` VALUES (5, 'Airport to Hotel');

millsy007 · February 10, 2009

Yes that is how the data looks

printf · February 10, 2009

This is a query that returns the following...


/*
*  1. return results for a given day ordered by coach_id asc, route_id asc
*/

$query  = "SELECT tb2.id, tb1.depart_dttm, tb2.coach_id, tb2.route_id, 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;";

It would return this...


id 	depart_dttm 	coach_id 	route_id 	occupancy 	route_desc 	passengers
1 	2009-02-10 09:00:00 	1 	1 	0 	hotel to airport 	 
2 	2009-02-10 09:00:00 	1 	2 	0 	airport to city center 	 
3 	2009-02-10 09:00:00 	1 	3 	0 	City Center to Downtown 	 
4 	2009-02-10 09:00:00 	1 	4 	1 	Downtown to Airport 	Tom
5 	2009-02-10 09:00:00 	1 	5 	0 	Airport to Hotel 	 
6 	2009-02-10 12:00:00 	2 	1 	0 	hotel to airport 	 
7 	2009-02-10 12:00:00 	2 	2 	0 	airport to city center 	 
8 	2009-02-10 12:00:00 	2 	3 	0 	City Center to Downtown 	 
9 	2009-02-10 12:00:00 	2 	4 	2 	Downtown to Airport 	Bill|Mark
10 	2009-02-10 12:00:00 	2 	5 	0 	Airport to Hotel 	 
11 	2009-02-10 16:00:00 	3 	1 	0 	hotel to airport 	 
12 	2009-02-10 16:00:00 	3 	2 	0 	airport to city center 	 
13 	2009-02-10 16:00:00 	3 	3 	0 	City Center to Downtown 	 
14 	2009-02-10 16:00:00 	3 	4 	0 	Downtown to Airport 	 
15 	2009-02-10 16:00:00 	3 	5 	0 	Airport to Hotel 	 
16 	2009-02-10 22:00:00 	4 	1 	0 	hotel to airport 	 
17 	2009-02-10 22:00:00 	4 	2 	0 	airport to city center 	 
18 	2009-02-10 22:00:00 	4 	3 	0 	City Center to Downtown 	 
19 	2009-02-10 22:00:00 	4 	4 	0 	Downtown to Airport 	 
20 	2009-02-10 22:00:00 	4 	5 	0 	Airport to Hotel

All the passengers in a given route_id will be split by a '|', so all you would do is explode ( '|', $row['passengers'] ); in your while loop to get an array list of passengers!

If you don't want to use aliases in your query (so you can see what tables are being used clearly), change the query to...


$query  = "SELECT journey.id, coach.depart_dttm, journey.coach_id, journey.route_id, journey.occupancy, route.route_desc, ";
$query .= "(SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) ";
$query .= "FROM passengers WHERE journey_id = journey.id) AS passengers FROM coach LEFT JOIN journey ON ";
$query .= "( coach.id = journey.coach_id ) LEFT JOIN route ON ( journey.route_id = route.id ) ";
$query .= "WHERE DATE(coach.depart_dttm) = '2009-02-10' ORDER BY journey.id ASC, journey.route_id ASC;";

millsy007 · February 11, 2009

Thats great thanks

What would be the best way to display these results, currently I am setting each result as a variable and then outputting each of them in a table:

for example:


$qry_result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($qry_result)){
$name = $row[passenger_name];	
}

}


//Build Result String
$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>Beach Hostel to Schipoll</strong></td>
                        <td valign=top><table width=100% border=1 cellspacing=0 cellpadding=0>
                          <tr>
                            <td>$name</td>

Is this a sensible approach to getting the query result into a table?

Sign In

Relational Database Records into a HTML Table

Recommended Posts

millsy007

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

millsy007

Link to comment

Share on other sites

printf

Link to comment

Share on other sites

millsy007

Link to comment

Share on other sites

printf

Link to comment

Share on other sites

millsy007

Link to comment

Share on other sites

printf

Link to comment

Share on other sites

millsy007

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information