PHP - Calculate Appointment Times

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.
Edited by mac_gyver, 16 August 2014 - 09:04 AM.
removed link, not relevant to post

This topic has been moved to Miscellaneous.

http://www.phpfreaks.com/forums/index.php?topic=348851.0

Hello all,

I'm collecting date/time information via a form and would like to convert the info into something that I can stick in my MySql database (a timestamp???).  I'm collecting the current month (variable name: month-- of the form "mm"), day (variable name: day -- of the form "dd"), year (variable name: year -- of the form "yyyy"), time of day (variable name: time -- of the form "h:00"), and before or after noon (variable name: ampm -- of the form "am" or "pm").  Any suggestions as to how to change this into a quantity that I can store as a value and then use to compare to other date/times would be appreciated.

Thanks!

Hi all

I am trying to edit a piece of code to change possible times for a booking system.

This is the code I have:

$count=0;
               for($b=$startTime;$b<$endTime;$b++){
                  if($count==$tempVar2){ $availability .= "</td><td align='left' valign='top'>"; }   
                  if(in_array($b,$reservedArray)){
                     $availability .= $b.":00 ".($b<12?"am":"pm")." - ".($b+1).":00 ".($b+1<12?"am":"pm")." - Booked.<br>";
                  } else {
                  $availability .=$b.":00 ".($b<12?"am":"pm")." - ".($b+1).":15 ".($b+1<13?"am":"pm")."- <input type=\"checkbox\" value=\"".$b."\" name=\"time[]\" ><br>";
                  }
                  $count++;
               }

It gives me the following times:

09:00 am - 10:15 am
10:00 am - 11:15 am
11:00 am - 12:15 am
12:00 pm - 13:15 pm
13:00 pm - 14:15 pm
14:00 pm - 15:15 pm
15:00 pm - 16:15 pm
16:00 pm - 17:15 pm

How can I change it so I can have the following times available:

10:00 am - 10:30 am
10:45 am - 11:15 am
11:30 am - 12:00 pm
12:15 pm - 12:30 pm
etc...

Many thanks for you help
Pete.

I need help with a query.   I've got a table that stores references to calls that come in to our office. It has a datetime field, and for the purposes of the query, it's the only thing in this table that is important.   I've got another table that stores appointment information. The appointment start time is also a datetime field. For this query, this start time is the only thing in this table that is important.   I need to know how many times in the last year there was a day when we had at least 150 calls come in and made at least 1 appointment.

SELECT COUNT(*)
FROM appointments a
LEFT JOIN (
  SELECT DATE(time)
  FROM calls
  WHERE <there are at least 150 calls on a day>
) c 
ON DATE(a.start_time) = c.time
WHERE a.start_time >= CURDATE() - INTERVAL 1 YEAR 

To make things difficult, I guess the time would have to be converted to a day, because I'm looking for all days where there was an appointment, and the datetime field is too specific.   I've been looking online for 30 mins or so, and was hoping somebody here could point me in the right direction. I'm not making much progress by myself.
Edited by sKunKbad, 06 October 2014 - 01:17 PM.