Inserting Records into Relational Database

[millsy007]

 

I want to fill my database with a coach shuttle timetable/schedule (to take people between airport and hotel). The Shuttle database has the following tables

 

Shuttle (indicating a shuttle run)

id

depart_dttm

 

journey (between two particular stops on a shuttle run. eg airport to city center, city center to hotel)

id

shuttle_id

route_id

occupancy

 

route (to show the description for a particular journey, this has been populated)

id

description

 

There is a shuttle that runs 4 times daily departing at 09:00, 12:00, 16:00 and 22:00 So for each day from now until the end of the year these dates and times would need to be filled in the shuttle table.

 

Then for each shuttle there will be 5 journeys that will need to be filled, as each shuttle always makes each of these 5 journeys:

 

description id

hotel to airport 1

airport to city center       2

city center to Downtown   3

Downtown to airport     4

airport to Hotel 5

 

 

I would like therefore to insert these values so all the shuttle runs and respective journeys are scheduled in, would a well constructed mysql insert query do this?

 

[printf]

I don't know why journey needs a 'id', shuttle_id references the Shuttle table and route_id references the route table, so there is no need for id, drop it before running this script!

 

Quick example...

 

<?php

$date_start = '01/30/2009';

$date_end = '12/31/2009';

$runs_per_day = array ( 9, 12, 16, 22 );

$routes = array ( 1, 2, 3, 4, 5 );

$dh = 'localhost';
$dn = 'coach';
$du = 'user';
$dp = 'pass';

mysql_connect ( $dh, $du, $dp ) or die ( mysql_error () );

mysql_select_db ( $dn );


$date_start = strtotime ( $date_start );
$date_end = strtotime ( $date_end );

$id = 1;

while ( $date_start <= $date_end )
{
	foreach ( $runs_per_day AS $departure_time )
	{
		mysql_query ( "INSERT INTO Shuttle VALUES ( " . $id . ", FROM_UNIXTIME ( " . ( ( $departure_time * 3600 ) + $date_start ) . " ) );" ) or die ( mysql_error () );

		foreach ( $routes AS $route )
		{
			mysql_query ( "INSERT INTO journey VALUES ( " . $id . ", " . $route . ", 0 );" );
		}

		$id += 1;
	}

	$date_start += 86400;
}
?>

 

It should work as long as your columns are all type INT (tiny or big) and depart_dttm is datetime. But drop "id" from your "journey" table before running this script!

[millsy007]

Hi thanks I need the id in journey too as i will have a passenger table that will have:

 

name

journey_id

 

so I can print out a schedule showing which passengers are on each trip. Sorry should of mentioned that.

Can I just add a $idjourney = 1; and use this in a similar way?

[millsy007]

I am getting the error

FUNCTION beachh_shuttle.FROM_UNIXTIME does not exist