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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/ Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 what does the table structure look like? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380057 Share on other sites More sharing options...
RamoneDaBone Posted September 22, 2012 Author Share Posted September 22, 2012 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380089 Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 what format is the date? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380105 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380107 Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 Then how do you know that 4232 is 9:00am if there is only the date and the id? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380109 Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 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. Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380110 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? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380111 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. Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380112 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? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380113 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). Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380114 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!!!!!!!!!!!!!! Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380116 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380117 Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 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. Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380118 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? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380119 Share on other sites More sharing options...
Jessica Posted September 22, 2012 Share Posted September 22, 2012 I assumed it was June 2013? Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380121 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380122 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380127 Share on other sites More sharing options...
Barand Posted September 22, 2012 Share Posted September 22, 2012 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 Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380131 Share on other sites More sharing options...
RamoneDaBone Posted September 23, 2012 Author Share Posted September 23, 2012 Thanks for the help. The relationship isn't hard to derive...I'm also an Academic from the Math World. Link to comment https://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/#findComment-1380200 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.