Page 1 of 1
HTML/PHP/MySQL Weekly Schedule Question
Posted: Wed Jun 27, 2007 8:30 am
by eric788
Hi,
I'm working on a PHP form that submits it's data to a MySQL Database, and that's straightforward for me. What I'm hoping to get here is help with an idea as to how I can ask a user for their schedule (the end goal is to get their availability in order to schedule them at times they can work) on a half-hour basis, and then later, reproduce the schedule they have chosen to see their availability (all the times they haven't scheduled as busy from 7am until 2am). See, right now I have a clunky way of doing it, and it's a pain for users to fill out. It involves a ton of checkboxes and the users simply check each half-hour checkbox if they are unavailable to work at that time. So what I ended up having in a 7 day wide, 7 am until 2am long table on the form, filled with checkboxes
Does anyone have any idea how I could get users to input their schedule for a week on a semi-hourly basis that looks nicer and doesn't add an extra page and a half to my form? Obviously I need to submit their schedule to the database so I can recall it and possibly query their availability.
Thanks,
I know this must seem a silly question, I'm just stuck here.
Posted: Wed Jun 27, 2007 12:09 pm
by ReverendDexter
Are you looking for a better interface, a better backend, or both?
Posted: Thu Jun 28, 2007 9:44 am
by eric788
Reverend, I'm asking for both. I'm using a boolean checkbox for each possible time Monday through Sunday between and including 7am until 2am the next day, that equates to approximately 260 checkbox form elements on the page that are then entered into the database for each user when they submit their schedule. Again I'm looking to get their times they cannot work in half-hour segments without using all of these checkboxes, and in the backend, by not entering all of these times that they are free, but I need to be able to reproduce their schedule and query it against others. What I'm doing now works, it just seems very clunky to me, but I'm stuck and thinking outside of the box I've built for myself.
ReverendDexter wrote:Are you looking for a better interface, a better backend, or both?
Posted: Thu Jun 28, 2007 1:51 pm
by califdon
That's a little bit like a couple of scheduler programs I've written, mostly using MS Access. It's a hairy problem to get a good user interface, especially with half-hour intervals. Here are some thoughts that occur to me:
I think I'd start with a conceptual framework that the entity that we're concerned with is the half hour interval. That would lead me to design a table that has fields like:
ID
Day
StartTime
EndTime
PersonID
Now you can ask the user to enter the day, start time and end time of each "chunk" of time, then you can calculate how many half-hour records to create when the form is submitted. Yes, it will generate a whole lot of these records, but that is no problem.
When displaying these records, once again you would need to recalculate the "chunks" by looking at successive half-hour records to see if they are contiguous. Sounds messy, but once you develop the algorithm, it's only a function call.
The real problem is how to design a form that lets the user enter a series of such "chunks" of available time. This is where your application is different from the ones I've done (which had to do with scheduling meeting rooms, and such). I don't think it would be very good to do them one-at-a-time, because it would be hard for the user to keep track of what they had entered or forgotten. Perhaps a form could have some arbitrary number of data entry lines that is reasonable for the application, something like:
+-----------------+--------+-------+
| Day of Week | Start | End |
+-----------------+--------+-------+
|
+-----------------+--------+-------+
|
+-----------------+--------+-------+
|
+-----------------+--------+-------+
|
+-----------------+--------+-------+
|
+-----------------+--------+-------+
|
+-----------------+--------+-------+
Maybe those could all be dropdown boxes, to keep the times formatted properly.
Just some initial thoughts. Hope they help.
Posted: Thu Jun 28, 2007 3:40 pm
by idevlin
Do the user's enter their non-working schedule for the entire week at a time, or will they submit, say, Monday until Wednesday, come back later and then finish it off?
Also, are the half hours for an entire day, ie 24 hour, or a working day?
Posted: Fri Jun 29, 2007 12:20 pm
by eric788
First of all, thanks Don for your detailed post above. I'm still sifting through the ideas you suggested, particularly of having them enter times with drop downs, however I wanted to let you know that I really appreciate the time you took to respond and share your experience with the subject of my problem. Thank you sir.
IDevlin, to answer your questions: users enter their schedule one time, and if they need to update their schedule, they update the original schedule they submit by using the same form. I currently replace into the table their busy times if they refill out their schedule, but generally this is their consistent weekly schedule (it's purpose is not to account for the occasional personal time they may need. Each of these employees are students and for many of them, this is their class schedule. They can also put any other time they aren't available, but this should be their primary schedule for several months). The hours of operation are from 7am until 2am, so that is the time frame they need to work with in determining their schedule. (the time between 2am and 7am is not available.)
idevlin wrote:Do the user's enter their non-working schedule for the entire week at a time, or will they submit, say, Monday until Wednesday, come back later and then finish it off?
Also, are the half hours for an entire day, ie 24 hour, or a working day?
Posted: Fri Jun 29, 2007 8:19 pm
by califdon
You're welcome, Erik. I have given a bit more thought to your challenge:
I'm visualizing a sort of matrix of drop-down boxes on the data entry screen, three wide and some arbitrary number vertically. Each row on the screen would represent a "chunk" of unavailable time on a particular day. The leftmost drop-down in each row would permit selection of the day of the week, the middle one would permit selection from a list of times in half-hours from 7 am to 2 am, like this: 07:00, 07:30, 08:00, etc. and so would the rightmost drop-down, the middle being the beginning of a chunk of unavailable time and the right one the end of the chunk. You would have to do a little experimenting on paper with typical schedules to see how many rows you would need.
I haven't worked it through, but I think that's one way I might approach it.
When you processed an input, you would need to calculate for each "chunk" how many half-hour instances it represents, which shouldn't be very hard, and then write those records to the table. I think it would be pretty flexible in future processing, although displaying it would require a sort of inverse algorithm to combine the records in the table to display the schedule, if you need to do that.
It's possible that it would be simpler to store the start and end times of each chunk in the record, instead of having, say, 4 records to represent a two hour chunk of unavailable time. I'd try analyzing that approach, too. I guess I'm hung up on the first method because of what I was doing with meeting room scheduling. In that application, having discrete records for each half-hour made it easy to display a graphic chart, because there either was or was not a record for every half-hour period on the chart.
Let us know how you work it out.