PHP Bus TImetables

[miles_rich]

Has anyone ever come accross a php bus timetable?? i am thinking about writing one, but i have no idea how to structure the database??

Has anyone got any ideas???

[okuto1973]

It's not so difficult.........

CREATE TABLE bus (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
ALTER TABLE bus ADD busnumber INT;
ALTER TABLE bus ADD stopplace VARCHAR(255);
ALTER TABLE bus ADD time TIME;

[OhLordy]

Not sure how well I've thought this through, but here goes (feel free to pick holes)

CREATE TABLE route (
    route_id INT(5) NUT NULL PRIMARY KEY AUTO_INCREMENT,
    route_name VARCHAR(255) NOT NULL,
    route_desc VARCHAR(255) NOT NULL);

CREATE TABLE route_stop (
    unique_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    route_id INT(5) NOT NULL,
    stop_id INT(5) NOT NULL,
    order TINYINT UNSIGNED NOT NULL,
    time INT(11) NOT NULL);

CREATE TABLE stop (
    stop_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    stop_name VARCHAR(255) NOT NULL,
    stop_desc VARCHAR(255) NOT NULL);

There are two ways of tackling this as far as I can see, you can either let the route ID be unique for each permeation of route ie the 501 leaving a at 9am is has a different route id to the 501 leaving a at 10am. The other way is to have all 501 services under the same route_id and set order/time as unique. Then when you want the next bus to leave a when the time is 9 you would do

select route.route_name as route_name from route, route_stop, stop where route.route_id=route_stop.route_id && route_stop.stop_id=stop.stop_id && stop.stop_name='a' && route_stop.order='1' && route_stop.time>now() ORDER BY route_stop.time 

NB
1. I'm sure there are much better time column types that can be used, never really looked into them
2. If you get the indexing and joining right you should be able to get this into a blisteringly quick setup.
HTH
Rob

[okuto1973]

CREATE TABLE route (
route_id INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
route_name VARCHAR(255) NOT NULL,
route_desc VARCHAR(255) NOT NULL);

CREATE TABLE route_stop (
unique_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
route_id INT(5) NOT NULL,
stop_id INT(5) NOT NULL,
order TINYINT UNSIGNED NOT NULL,
time INT(11) NOT NULL);

CREATE TABLE stop (
stop_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
stop_name VARCHAR(255) NOT NULL,
stop_desc VARCHAR(255) NOT NULL);

🙂