[RESOLVED] time() ain't on my side, and other cliches

ATS16805

i'm seeking help for a page that supposed to return db results on a query about bus-route times-- part of what's going to be a "Journey / Trip Planner" for a small-city public transport bus co. this particular piece is the first that any of my "back-end" and my ("finished") database will finally function to produce a result of tangible, useful data. i have spent all of the time thus far (since early Dec) testing small bits of code, creating input forms to transfer Excel data into the db, etc-- all toward getting the MySQL db where i need it to be for a successful app to function.

i've finally reached the point of "readiness" for trying to crunch my data, and finally begin developing the actual front-end-- albeit, still very much just trying stuff to see what happens.

i posted 2 informative pages showing my db tables. please observe them, and then read on.

shown in my illustration at the link, you see that i am working w/ two particular bus-routes, which service two different areas of town, but a bus patron might ride busA to busB and continue his or her trip. i need to make use of that data shown in the illustration to produce estimations about when the bus patron might want to stand at his neighborhood/ street's bus-stop, and also to inform that potential patron when he or she might arrive at her destination.
EDIT: and to also allow for a third stop being a "changeover" from busA to busB, a most critical aspect of the app
EDIT2: for the record-- it's the manipulation of a particular bit of data that i sought here-- but this thread went off on a particular issue related to what mr thorpe sees as a critical flaw in my db design. i'm still interested in the original inquiry i've posted here.

below is my first attempt at crunching this data. this is supposed to show me all of the available times for a single bus-stop (eg. bus-patron "Joe" seeking info about his nearest bus-stop, identifed by code "wc13e" [each represents a unique geographical location] ). my thoughts are that i need to know every possible time of day the bus will pass "Joe's" stop, so he knows his options.

My problem w/ the code is that it doesn't report AM and PM correctly, although it is showing the proper times as queried from the fields "rt01 - rt22" as shown in Fig 2.1.

here's what i have:

$htmlblock = "<p>The route times for bus stop $stop_code are:</p>
<ul style=\"list-style-type:none;\">";

for($x=1;$x<=22;$x++) {

	$pad = str_pad($x, 2, "0", STR_PAD_LEFT);
	$fieldname=${"rt".$pad}; 

	$blue = " style=\"color:blue;\">";
	$red = " style=\"color:red;\">";

	$time = strtotime($fieldname);

	if(($x >6) && ($fieldname >= '12:39:00')) {
		$fieldname = $fieldname." pm";
		$fieldname = "$blue".$fieldname;
	} else {
		$fieldname = $fieldname." am";
		$fieldname = "$red".$fieldname;

	}

/* if( ($x <= 6) && ($fieldname < '12:39:00') ) {
	$fieldname = $fieldname." am";	

} elseif( ($x >= 6) && ($fieldname >= '12:39:00') ) {
 */

$htmlblock .= "<li".$fieldname."</li>";  

		}		

$htmlblock .="</ul>";

one concern that i have is my logic if(($x >6), which bases the AM / PM not on time value, but on my knowledge of which trip revolution the bus is on throughout the day-- this of course is unreliable, as some buses will make more or less revolutions (routes, trips... whatever you want to call how many times they run per day), which are represented by the fields referenced above in fig 2.1

that's all i have for now. thank you so much for reading. i am very eager to read any replies. thanks again!!!

by the way-- if you recognize if i'm headed for potential disaster, etc., please advise however you'd like.
(please consider also that i'm relatively new to programming-- not using as an excuse, but just keep in mind i haven't much experience so i often overlook the obvious, or simply am unaware of obvious solutions.)

thorpe

Firstly... one thing I would suggest is changing the schema a little. What if the bus company decides they want to stop at a stop more than the designated 22 times? You'll need to add more fields. Never a good idea to limit the application.

I would setup a table for stops. (You'll need to excuse my syntax, haven't used mysql in a while)

CREATE TABLE stops (
  id INTEGER AUTO NUMBER,
  code VARCHAR(20)
)

Then another for the timetable.

CREATE TABLE timetable (
  id INTEGER AUTO NUMBER,
  stop_id INTEGER, -- foriegn key
  stop_time TIMESTAMP
)

Now some sample data.

stops:

id | code
1  | wc131d
2  | wc131e
3  | w1

timetable:

id | stop_id | stop_time
1  | 1          | 00:00:00
2  | 1          | 07:14:00
3  | 1          | 08:19:00
4  | 1          | 09:24:00
5  | 1          | 10:29:00
6  | 1          | 11:34:00
7  | 1          | 12:39:00
8  | 1          | 13:44:00
9  | 1          | 14:49:00

And so on....

This allows you to add as many schedueled stops as could ever be required. You could also add more locations quite easily, and because where using a timestamp to store the times, your query to retrieve all the times for stop with an id of 1 would be...

SELECT stop_times FROM timetable WHERE id = 1 ORDER BY stop_times;

The timestamp will also eliminate your other problem.

Hope this gives you some ideas.

ATS16805

Thorpe, profuse thanks for your thoughtful reply!

hmmm... i thought i have planned for... what would you call that "modularity"?...meaning that i thought i would be able to add stuff, no? i'm not sure i'm seeing why i can't.

just to be sure we're on the "same page" so to speak, please take a look at the three table descriptions where i have fig.1.2-master table showing auto-increment and stop-id, fig1.3, the timetable showing stop-id, autoincrement, and timestamps in fields respective to the amout of bus revolutions per day, and then fig.1.1 which shows the characteristics of each stop-id, so i have data on which to match queries.

is this somehow different from what you've suggested? please explain.

thanks again!
🙂

thorpe

Your method is almost the complete oposite to mine. Your way your locked in to each bus stop only ever having a bus arrive a maximum of 22 times a day. What if the company decides that instead of once an hour (or close to it), they are going to double there timetable and stop every half hour. You will run out of fields, my way, you just add more rows. You could have a bus arrive every minute if need be. Your way, there can only ever be 22 times in a day.

I meen sure... You could always add more fields but this meens restructuring the database, and should be avoided.

ATS16805

thorpe wrote:
You could always add more fields but this meens restructuring the database, and should be avoided.

right-- i guess that's what my plan was...

believe me, i definitely want to start off on the right foot-- right now i'm more eager to achieve a "solid backend" than i am focused on pretty query reports showing data output and searchable bus-stops.

currently-- i'm kinda confused as to how you're achieving the allowance for expansion while maintaining a cohesive link to the master table primary key-- in such a way that restructuring the database will never need to occur. there's something i must be missing here-- i'm going to need to take a more thorough look at what you've suggested.

by the way-- i'm not beyond switching to (or trying) PostgreSQL, should anyone strongly advise that for this app. from the testimonials i've read, i picture myself moving in the postgres direction eventually-- but i simply haven't tried anything yet (at risk of derailing the thread-- the point is that the app is still very much "on the drawing board", wouldn't you agree?)

EDIT: by the way, thorpe, i realize "what" you're suggesting (including an awareness of why one would want to allow, for example, the ability to accomodate for a revised bus schedule), i just don't realize "how" to get there vs my current setup.

thorpe

Maybe some more fake data will help explain it better. Here is the timetable table again, this time with 22 times (your limit) assigned to the stop with the id of 1. Remember, the 1 is related to wc131d by meens of the stops table.

id   | stop_id | stop_time
-------------------------------------
1    | 1          | 01:00:00
2    | 1          | 02:00:00
3    | 1          | 03:00:00
4    | 1          | 04:00:00
5    | 1          | 05:00:00
6    | 1          | 06:00:00
7    | 1          | 07:00:00
8    | 1          | 08:00:00
9    | 1          | 09:00:00
10  | 1          | 10:00:00
11  | 1          | 11:00:00
12  | 1          | 12:00:00
13  | 1          | 13:00:00
14  | 1          | 14:00:00
15  | 1          | 15:00:00
16  | 1          | 16:00:00
17  | 1          | 17:00:00
18  | 1          | 18:00:00
19  | 1          | 19:00:00
20  | 1          | 20:00:00
21  | 1          | 21:00:00
22  | 1          | 22:00:00

This shows a bus stopping at bustop #1 every hour on the hour from 1AM until 10PM, thats 22 times. Now, with your design, thats all you get, but lets say we want a few extra stops in peak hours. Say between 5PM and 8PM we want the bus to stop every half hour.

We simply insert the new records.

INSERT INTO timetable (stop_id,stop_time) VALUES (1,'17:30:00');
INSERT INTO timetable (stop_id,stop_time) VALUES (1,'18:30:00');
INSERT INTO timetable (stop_id,stop_time) VALUES (1,'19:30:00');

Now our data looks like...

id   | stop_id | stop_time
-------------------------------------
1    | 1          | 01:00:00
2    | 1          | 02:00:00
3    | 1          | 03:00:00
4    | 1          | 04:00:00
5    | 1          | 05:00:00
6    | 1          | 06:00:00
7    | 1          | 07:00:00
8    | 1          | 08:00:00
9    | 1          | 09:00:00
10  | 1          | 10:00:00
11  | 1          | 11:00:00
12  | 1          | 12:00:00
13  | 1          | 13:00:00
14  | 1          | 14:00:00
15  | 1          | 15:00:00
16  | 1          | 16:00:00
17  | 1          | 17:00:00
18  | 1          | 18:00:00
19  | 1          | 19:00:00
20  | 1          | 20:00:00
21  | 1          | 21:00:00
22  | 1          | 22:00:00
23  | 1          | 17:30:00
24  | 1          | 18:30:00
25  | 1          | 19:30:00

And if we SELECT it in order using a query like...

SELECT * FROM timetable WHERE stop_id = 1 ORDER BY stop_time ASC;

We get...

[code]

id   | stop_id | stop_time
-------------------------------------
1    | 1          | 01:00:00
2    | 1          | 02:00:00
3    | 1          | 03:00:00
4    | 1          | 04:00:00
5    | 1          | 05:00:00
6    | 1          | 06:00:00
7    | 1          | 07:00:00
8    | 1          | 08:00:00
9    | 1          | 09:00:00
10  | 1          | 10:00:00
11  | 1          | 11:00:00
12  | 1          | 12:00:00
13  | 1          | 13:00:00
14  | 1          | 14:00:00
15  | 1          | 15:00:00
16  | 1          | 16:00:00
17  | 1          | 17:00:00
23  | 1          | 17:30:00
18  | 1          | 18:00:00
24  | 1          | 18:30:00
19  | 1          | 19:00:00
25  | 1          | 19:30:00
20  | 1          | 20:00:00
21  | 1          | 21:00:00
22  | 1          | 22:00:00

This would be no different if there where records for bustop number 2,4, 88 whatever mixed in.

Hope this helps.... Its kinda hard to explain.

ATS16805

i see what you're doing there-- but how do i use that data w/ the greater picture-- that's where i'm confused

(one moment please while i figure out how to explain my plan for my method... so that is on the table at least)

thorpe

No differently to how you would have used your data. Imagine all the 1's are wc131d's if that makes it any easier.

ATS16805

i have that table though

ATS16805

i mean-- how is that different than fig 1.2 assuming i were to add a timestamp?

i think i need more time to think.
🙂

EDIT: i mean-- doesn't that existing table give me the ability to do w/ another table, what you've done... it's what i mean is i already have the "stop_id"

my point is-- how do i use it to make the comparisons necessary for the route planner-- if a person needs to know where a bus change-over (transfer / layover / whatever) can take place at what time, etc?

thorpe

Ok... your figure 1.2 is the equivelent of my stops table.

thorpe

i mean-- doesn't that existing table give me the ability to do w/ another table, what you've done... it's what i mean is i already have the "stop_id"

Yes. My timetable table is equivelent (should replace) your figure 1.3.

ATS16805

right-- i got that-- but, i'm having difficulty seeing how i switch from my former method (which now that i think about it in a rather confused state i'm in now that we've been discussing this, might not have actually worked) how i change my estimation technique to allow for telling a bus patron what time he needs to stand at the bus stop if he indeed must travel to a second bus stop to take a second bus to the final destination-- that's my problem w/ switching everything-- i can't see that part working...

like i said-- i need a minute to get my head straight on this...
(klank! sounds of gears rushing to halt... metal crunching)

thorpe

Another thing I just though about is that my method would be very easy to add different times for say the weekends or public holidays. We would simply need a day_type field.

0 = weekday
1 = weekday & weekend
2 = weekend
3 = public holiday

id   | stop_id | stop_time | day_type
------------------------------------------------------
1    | 1          | 01:00:00 | 0
2    | 1          | 02:00:00 | 0
3    | 1          | 03:00:00 | 0
4    | 1          | 04:00:00 | 0
5    | 1          | 05:00:00 | 0
6    | 1          | 06:00:00 | 1
7    | 1          | 07:00:00 | 1
8    | 1          | 08:00:00 | 1
9    | 1          | 09:00:00 | 1
10  | 1          | 10:00:00 | 1
11  | 1          | 11:00:00 | 0
12  | 1          | 12:00:00 | 0
13  | 1          | 13:00:00 | 3
14  | 1          | 14:00:00 | 1
15  | 1          | 15:00:00 | 1
16  | 1          | 16:00:00 | 1
17  | 1          | 17:00:00 | 1
23  | 1          | 17:30:00 | 0
18  | 1          | 18:00:00 | 0
24  | 1          | 18:30:00 | 0
19  | 1          | 19:00:00 | 3
25  | 1          | 19:30:00 | 3
20  | 1          | 20:00:00 | 1
21  | 1          | 21:00:00 | 1
22  | 1          | 22:00:00 | 0

I dont want to complicate things, but I hope you can see the flexbility.

ATS16805

oh yes-- but, i don't think you're hearing me. i've understood your table like 5 of your posts ago! hehehe

but, all i see is one stop ID. what about the second, third stop? (stop 2 = changeover, stop3 = destination)? that's what i need

thorpe

how i change my estimation technique to allow for telling a bus patron what time he needs to stand at the bus stop if he indeed must travel to a second bus stop to take a second bus to the final destination

Well im not sure what you had in mind earlier, but I dont really see how my theory would make it any harder.

Anyway, keep in mind this is just an idea. I dont profess to be a database designer, and Im sure there are probably others on this board with different ideas.

Anywho... I'll leave you to stew it over for a bit. Good luck.

thorpe

what about the second, third stop? (stop 2 = changeover, stop3 = destination)? that's what i need

Man... It makes no difference.

Have you got access to sqlite? And have you a literall copy of the timetable. If I get time (I'm in the middle of rebuilding my OS) I could maybe throw together a better example with some queries you can actually run.

ATS16805

thorpe wrote:
Have you got access to sqlite?

not that i know of. just MySQL. i think i may have d/l it though. i've heard of it. SQ Lite? r

ATS16805

http://sqlite.org/
?

btw-- i really appreciate it!!!

i feel so stupid cause i didn't get the chance to say "well, this is what i had planned...", cause i don't have that written up in a "forum friendly" summary, per se.

thorpe

I wouldn't bother with sqlite if you dont think yoiu have it. It comes with php5 and is a simple flat file db so it makes sharing data pretty easy. My postgres and mysql server are both out of action at the moment while I do this rebuild.

Anyway.... theres some food for thought there.