darkside619

mac_gyver - I should probably clarify how the process works, or how I want it to work. Each trainer has their own separate calendar which shows a dot on any day that they're available. You can see an example of what I mean if you go to this link as this is the calendar script/template that I want to use for my site with some changes like the color scheme, etc. Anyways, if you go to the link below and click back to November 2012 you will see that there are some sample events populated there. Any day that has a circle on it is clickable and will show a window pop up which fetches events from a JSON file. The date is the key and the value is the text of the link. http://tympanus.net/Development/Calendario/index2.html Some more info about this calendar: http://tympanus.net/codrops/2012/11/27/calendario-a-flexible-calendar-plugin/ My initial idea, which if I'm understanding correctly is what you're suggesting, was to send the user to a separate page after they click on a day and that page would display the available time slots for the personal trainer. The user could then book a date and the personal trainer would receive an email which asks them to confirm the appointment. I think this is cleaner and easier to implement than trying to load the events in the pop up window like you see in the example. Would I be correct in assuming though that even doing it that way would be too resource intensive as every time a user clicks on a personal trainer, the script would have to query their availability for each day to send to that JSON script so that the dots can appear on the calendar? If I have hundreds of people looking at different personal trainers at the same time, that would probably use up a lot of bandwidth and maybe slow the site down right? Also, how would I deal with an appointment that hasn't been confirmed yet? Would I create a separate table for unconfirmed appointments and then when the trainer confirms it move the record from unconfirmed into a confirmed table then delete it from unconfirmed? I've never made a calendar so I don't know what the best practices for making one are.

Okay, that makes sense. The script that I wanted to use displays events from a JSON file. The format of the file is like this: var codropsEvents = { var codropsEvents = { '11-23-2012' : '<a href="http://tympanus.net/codrops/2012/11/23/three-script-updates/">Three Script Updates</a>', '11-21-2012' : '<a href="http://tympanus.net/codrops/2012/11/21/adaptive-thumbnail-pile-effect-with-automatic-grouping/">Adaptive Thumbnail Pile Effect with Automatic Grouping</a>', '11-20-2012' : '<a href="http://tympanus.net/codrops/2012/11/20/learning-principles-for-improving-your-css/">Learning Principles for Improving Your CSS</a>', '11-19-2012' : '<a href="http://tympanus.net/codrops/2012/11/19/responsive-css-timeline-with-3d-effect/">Responsive CSS Timeline with 3D Effect</a>', '11-14-2012' : '<a href="http://tympanus.net/codrops/2012/11/14/creative-css-loading-animations/">Creative CSS Loading Animations</a>', '11-13-2012' : '<a href="http://tympanus.net/codrops/2012/11/13/baraja-a-plugin-for-spreading-items-in-a-card-like-fashion/">Baraja: A Plugin for Spreading Items in a Card-Like Fashion</a>', '12-25-2012' : '<span>Christmas Day</span>', '12-31-2012' : '<span>New Year\'s Eve</span>' }; The keys are the dates and the values can be anything, like a URL. What would you suggest is the best method to retrieve the info from MySQL and feed it to the JSON file? I can feed the values into a GET query but I think that would not be secure right?

I was trying to edit my post but for some reason I can't. Anyways, please discount all my questions in the above post except for this one: Lastly, I was confused by what you meant when you wrote: a.trainer, a.day Are those placeholders for the names of particular trainers and for specific days? I haven't seen that period syntax used like that.

Okay, Barand, I've read up on some MySQL and I understand your post better now. However, I don't think your solution accounts for time slots before the latest booking. So, for instance if a trainer is available from 9am to 6pm and a time slot from 12pm-1pm is booked, your solution starts from there and doesn't account for the 3 hour gap before noon. Also, I might be missing it, but where does your query break up the day into multiple chunks? So, if the training session is 1 hour long, there should be a list of results which show the trainers availability. For instance, assuming that the trainer is available from 9am to 6pm and there are no bookings for a particular day, and assuming that the training session is 1 hour long, then the user should see a list like this: This trainer is available from: 9:00 am - 10:00 am - Book this time slot! 9:15 am - 10:15 am - Book this time slot! 9:30 am - 10:30 am - Book this time slot! 9:45 am - 10:45 am - Book this time slot! etc., it would keep going until the trainers closing time. If there are slots booked, then it would skip those slots but show the remaining available time. Lastly, I was confused by what you meant when you wrote: a.trainer, a.day Are those placeholders for the names of particular trainers and for specific days? I haven't seen that period syntax used like that. Thanks!

I should also point out that the calendar application that I'm using reads events from a json file. If a day is available for booking a personal trainer then that day has a dot on it, indicating that there are time slots available. This is why I thought that I'd populate the json file from a mysql table rather than hard code it myself.

Well I'm new to making calendars so I figure I don't have it that well planned out but here is what I was thinking. Keep in mind that the intention is for this to be used for booking appointments with personal trainers. My plan is to create a table with 96 time slots of 15 minutes each, since appointments can vary in length and be either 15 minutes, 30 minutes, 45 minutes, 1 hour, 2:30 minutes, etc. long. So, what I would do is fill in the dates for a year in advance and set each time slot to 0 or 1 depending on the schedule of the personal trainer. A 0 means that he is not available in that 15 minute time slot whereas a 1 would mean that he is. So, lets say that a user chooses a training session that is 1 hour long. Then, I would query the database to see where 4 slots are available in succession that equal to 1. So, lets say for instance that from 2pm-3pm the trainer is available. This would mean that 2:00 has a value of 1, 2:15 has a value of 1, 2:30 has a value of 1, and 2:45 has a value of 1. So, my php script would query the database and add all the time slots in a loop to see where four #1 slots are next to each other. I realize that this would make my application query the database a hell of a lot since each day of the calendar would have to be searched like this. I'm open to ideas though.

Hello. I wanted to make a table where the dates are listed going forward at least one year. So, at least 365 records, but each date should be unique and should start from today until one year from now. I tried to make a date column and make it a primary key. However, an error comes up when I try to insert more than one record. This is for a calendar that I'm building so I need the dates listed one year in advance and it would be great if new dates could be added without my having to manually go in there and insert new records to stick to my 1 year in advance rule. For that aspect I might have to create a php script where the date records are added every time a user logs in, to make sure they are up to date, but anyways what I don't understand is why I can't make a table with 1 year of date records in advance, where each row has a unique date starting from today.

Thank you for the help Barand. I greatly appreciate it.

Barand thanks for the reply. I think you've nailed it but I'm having a difficult time understanding your queries. For instance, what do you mean by this: I'm new to MySQL so your query is difficult for me to wrap my head around, especially without comments. Where did @prevdate and @prevend come from? I didn't see either of those fields in the two tables.

Yes. Sorry, I tend to get carried away with descriptions. My question was at the very end. I want to know what is the best way to go about dealing with variable lengths of appointment times? I don't want to start and have to do it over again because I screwed up on the initial planning. Should I make a separate MySQL table for each trainer that lists their appointments and then a separate table for each type of appointment length and then compare the two to see where the appointments table has an empty spot? I figure that someone who is more experienced than I would have already tackled this issue and would know the perfect method to use. Please let me know if that's not enough info.

Hello. I'm trying to build a site where users can book appointments with personal trainers. I want to use the free and open source jQuery Full Calendar for the look and feel of the calendar since it looks pleasant and is responsive. <link removed> I want each personal trainer to have their own calendar which will show the days that they're available. When a user clicks on an available day then they will see a list of time slots that they can book in 15 minute increments. So, for instance lets say a trainer named Mike is available on Mondays-Friday from 8am-5pm. If a user comes to his calendar they will see that certain days are not fully booked and they can click on a day. Then when they click on a day they can see time intervals like this: Mike's available time slots for Tuesday August 25, 2014: 8:00 am-9:00 am - Click here to book appointment! 8:15 am-9:15 am - Click here to book appointment! 2:00 pm - 3:00 pm - Click here to book appointment! The page above can be a separate page from the calendar, but it would need to be synced with the calendar to be able to fetch the times and days that he's available for appointments. Then when the user clicks on a day, he can book an appointment and after the trainer confirms via email then the user will receive an email confirmation that his appointment is scheduled. While it's still being confirmed though, that time slot would need to be no longer visible to other users, so that there wouldn't be multiple bookings for the same time slot, to avoid those conflicts. So, the application would need to fetch the data from MySQL and use that to display the available days. If a day is not available then the user will not be able to click on it in the calendar. Also, the trainers should be able to set their schedule which will get updated to MySQL, so the schedule is normally recurring but they should also be able to change certain days if they feel like they can or cannot work on that day. Also, the appointments will vary by time, so a training session can last 30 minutes, 1 hour, 3 hours, 1 hour and a half, etc. and that would need to be taken into account when the user books an appointment because if the trainer has a 1 hour gap between 2pm and 3pm, but the session is for 2 hours, they should not be able to book that time slot, since it wouldn't make any sense. What makes this complicated is that the appointment lengths can vary. Otherwise, if every appointment was 1 hour long I would be able to just create a table for appointment times and do a query to see if they're booked and only show the booked ones. How can I do it with variable time lengths? Any help would be greatly appreciated.