RamoneDaBone Posted September 22, 2012 Share Posted September 22, 2012 I am creating a scheduling script in PHP from a mySQL database. My problem can be explained with the following example. I have 10 appointment cells in day x (0-9). Each appointment cell has an adjacent corresponding cell to describe whether the appointment is available or not (either 0 or 1...and call this NID). I will need to identify whether appointment t, t+1, t+2 and t+3 are all available. In other words, some appointments will require multiple time slots. How can I filter through the dumped sql data with PHP by looking at the NID and checking whether t, t+1, t+2, t+3 are available? I've tried enumerating the time slots and putting them in arrays but can't figure this one out. Thanks for any ideas Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 what does the table structure look like? Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 (edited) Lets say the table has 2 columns column 1 has a date and tid. for example.... june 1 9:00 am would be 4232, june 1 9:30am would be 4233, june 1 10:00am would be 4234 column 2 has a boolean, either 0 or 1. 0 indicates the time slot is available while 1 indicates that its not. through another language interacting with PHP i will send requests for appointments of different lengths. for example...i will need to book a 1.5 hour appointment on june 1 starting at 9:00am. so i will need to check whether 930 and 10 am are also available. do you need more info? thanks Edited September 22, 2012 by RamoneDaBone Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 what format is the date? Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 its being pulled from the database in this format. 2012-01-12 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 (edited) Then how do you know that 4232 is 9:00am if there is only the date and the id? Edited September 22, 2012 by Barand Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 (edited) Can you just post the actual table structure with some data? Edit: I take that back, rather than storing whether or not a "slot" is open, you should only store the reserved datetimes. Then if it doesn't exist, it's available. Edited September 22, 2012 by Jessica Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 Maybe I should make it more clear. September 22nd 2012 9:00am is 1 September 22nd 2012 9:30am is 2 September 22nd 2012 10:00am is 3 ... September 22nd 2012 4:30pm is 15 September 22nd 2012 5:00pm is 16. Then September 23rd 2012 9:00am is 17 September 23rd 2012 9:30am is 18 September 23rd 2012 10:00am is 19. Does that make sense? I was trying to reduce the number of parameters. I can create a simple formula that will translate todays date and time into an ID number (....like 18 for September 23rd 2012 at 9:30am) that will always hold true regardless of the time or date that I submit the request. Do you see how my issue lies in checking the row+n's depending on what type of appoointment it is? Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 If you just store the start and end time of the appointments, it's much easier. So the issue is with your design. Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 Jessica, I need to show availability to users. They will be viewing availability one week at a time starting from today. I'm not sure if I understand how I would implement your suggestion. Can you be more explicity with your explanation? Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 You should have a table of spots that are taken. That table should store the start and end time of an appointment, in addition to any other info that is important (but singular to that appointment). Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 Alright, I see what you mean. Thanks Jessica!!!!!!!!!!!!!! Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 I can create a simple formula that will translate todays date and time into an ID number june 1 9:00 am would be 4232 September 22nd 9:00am is 1 That's some formula Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 (edited) Not really, I get what he's saying. 09-22-2012 9am = 1, 9am to 5pm is 12 spots, I'm going to throw in an hour for a lunch break so 10 spots. take the current date and get the number of days between 9-22-2012 and now, multiply by 10, and add 1 for every half hour. it's a bad bad idea, but not a hard formula. Edited September 22, 2012 by Jessica Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 Thanks Jessica, I did maths at uni. How does it reconcile ID=1 being in September and ID=4232 being in june? Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 I assumed it was June 2013? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 That would give a 273 day difference and he said he has 10 slots a day Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 I dunno. You're right. Which is why he should do the other thing I said Quote Link to comment Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 (edited) ST ET | | -----------+-------------------+--------- | | --- s1--------------e1 | | | s2-----------e2 | Booking required from ST to ET | | | s3--------------------------e3 |-- If any of these booking exist | | | the time slot is not available | s4------e4 | | | | | ie s < ET AND e > ST | | --- Why the f*** can't it preserve spaces? Store your booking start and end time times as DATETIME type fields. If someone wants a booking from 2012-09-24 09:00:00 to 2012-09-24 10:30:00 then you can find if any of the conditions, in attached image, exist with this query SELECT COUNT(*) FROM bookings WHERE starttime < '2012-09-24 10:30:00' AND endtime >'2012-09-24 09:00:00' If the count is zero, the slots are free Edited September 22, 2012 by Barand Quote Link to comment Share on other sites More sharing options...
RamoneDaBone Posted September 23, 2012 Author Share Posted September 23, 2012 (edited) Thanks for the help. The relationship isn't hard to derive...I'm also an Academic from the Math World. Edited September 23, 2012 by RamoneDaBone Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.