PHP/mySQL Timetable

[d0nny]

I've asked this question previously with jpadie, who typically provided a very good answer, albeit very complex (for me!).

What I have is the need for a fairly stable weekly timetable, so this is not a full calendar solution.
I want a weekly timetable with entries on it that don't really change that often, apart from perhaps the addition or deletion of perhaps single entries (on a very irregular scale), and the ability to turn the entries on and off (active/non-active).

I have already developed a solution here:

http://bit.ly/hLYYGg

But the problem I now have is that I need to add another timeslot to this calendar and all of my entries are sequential - to add another timeslot in say at 11.30am creates a problem with my sequential timeslots. It would mean all the entries I currently have after the 11.30am slot would need to increase by one for the front end code to sequentially put the slots together! Make sense??

But I started thinking that there must be a 'timetable' solution out there rather than a full calendar solution.
The difference, in my mind, is that I don't need to specifically date entries. It is a weekly repetitive affair.

Any advice or suggestions?

 

[jpadie]

you have a choice:

1. either write a script or manually add a blank item to each of your entries to deal with the new timeslot. this is sub-optimal as you may need to move to another set of time slots in due course.

2. store the start time and length and day of each class in a week. you might also store, in a separate table, the start date and stop date of each of the events, so that you could insert changes relatively easily.

obviously, method two is somewhat closer to optimal.

then i would deliver each 'week' as a json object to a jquery calendar class like jquery_week_calendar by la mouette.

with this you should be able to get something up and running with basic flexibility within 30 mins or so.

 

[d0nny]

Thanks jpadie

I don't actually care about the durations of any events, only that they are sequential.
So, from my example, if a class starts are 10.30am and another one at 11.15am and it is thee next class in the sequence, then just display it.
My data at present simply has a sequential timeslot associated with the class. So in the above example where I simply have two classes, the 10.30 class is slot 1 and the 11.15 class is slot 2.
My problems start when I start a new class which starts at say 11am! This then suddenly becomes slot 2 and the 11.15am class needs to become slot 3.

All of the info on the example timetable from the previous link is stored in a mySQL database, so each slot is effectively hard-coded (in the DB table) as is the day (number).
I now want to add two new timeslots!

I could just go and add two new slots and adjust the other timeslots to increment by 2 (or 1 depending on where they slot in), but that's hard work (well, probably not that much work actually!).

But I was thinking there must be a more flexible way of doing this for the future.
I'm sure school timetables are similar... repetitive.

 

[jpadie]

do you store the sequence ID?

 

[d0nny]

Yep.
I currently have 13 sequential events so each event has a day number and a sequence number.

But now I need to add a new event within the sequence.

 

[jpadie]

assuming the sequence IDs are 1 2 3 etc why not

1. alter the table schema so that the column is a float type rather than int (if necessary)

2. number the next in sequence as 4.1 etc so that it fits between 4 and 5

3. order your query by sequence ID.

this is a kludge. you would do better to adopt a more flexible structure from the get go

 

[d0nny]

Thanks jpadie
That seemed to work, but I have other complications and I feel I have over-engineered my timetable solution.

I suppose what I'm really asking is if there is a simple timetable solution, or how would you go about producing a timetable solution, bearing in mind I have no requirement to know the actual date of event or the duration, just that it appears (repetitively) on the same day every week and at the same time.

 

[jpadie]

XML perhaps. Or just as a string.

However I cant reconcile the statement that you do not need to know the duration or date of an even with the nature of a timetable. Timetables should surely be flexible enough to allow certain weeks to have no lessons or different lessons? They should be changeable on the fly. Temporary differences should be able to be recorded for particular future weeks etc. Just like school timetables. Instructors should be able to be swapped out.

I suspect that by not employing a standard calendaring ( or perhaps room booking) solution you are actually creating far more hassle and work for yourself than trying to kludge something.

 

[d0nny]

Agreed that the timetable does need to be flexible and perhaps knowing the duration is useful only if you don't want's any overlapping of events.
Overlapping doesn't bother me either. I set the times of classes and I would know if I set a class for 10am (which lasts for say 45 mins). I would know not to start a new class to at least 10.45am.
And I'm not recording instructors (does that make a difference to a school timetable?? Surely the class/event will still happen whether Mr A. or Miss B. was taking it?

And the reason I don't want to employ a full calendaring system is I'm not interested in dates. The whole week is repetitive so storing dates is over-complicating the process. I simply need to know that certain events happens on a certain day, so say Judo happens every Monday at 10am.
If I store this as a calendar item I need to associate a date with it, which is specific. I would then need to enter a Judo class for every week. No?

 

[jpadie]

Surely the class/event will still happen whether Mr A. or Miss B. was taking it?

perhaps. but if so then Mr A's class will need to be rescheduled or joined to another class or whatever. ie any solution should cater for the absence of replacement teachers.

I would then need to enter a Judo class for every week. No?

yes. but decent calendar classes will allow for repetition for an arbitrary number of weeks/intervals.

and the point is that there may well be some 'Mondays' on which the judo class does not happen. It may be a public holiday, the guy may break his leg and the replacement (for the next 8 weeks) might only be available on Tuesdays

still, it's your application so you are best placed to determine what levels of inflexibility are within the functional boundaries of your needs.

 

[d0nny]

On the Monday's when the class doesn't happen, I simply switch the event off (I have an eventActive flag in my database for each event).
When this is 'inactive', the resulting calendar/timetable simply doesn't display the event.

Hmmm... I really think this is easier than a full calendering system but I might have to bow to your superior knowledge.
I'll sleep on it!