Appointment booking script for calendar

[darkside619]

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 August 16, 2014 by mac_gyver
removed link, not relevant to post

[mac_gyver]

besides listing what you want, do you have a specific programming question or a problem you need help with?

[darkside619]

besides listing what you want, do you have a specific programming question or a problem you need help with?

 

 

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.

[mac_gyver]

there are existing resource availability/resource reservation scripts that probably do this in some fashion (likely for reserving/booking rooms, rather than a trainer, but the logic is the same.)

 

you would need a table to hold the resource (trainer) availability schedule, all resources in stored in the same table, using a resource id to identify which rows are for each resource. for reoccurring schedules, you would need to store the definition of the schedule (Mike is available on Mondays-Friday from 8am-5pm) and evaluate it, storing the resulting dates and times in the availability schedule table, as needed (any query displaying data with a date higher than the latest stored date would evaluate the definition to populate dates up to at least the latest display date.)

 

you would have a second table to hold resource reservations, with the resource id, the id of who is requesting the resource, the date, start time, end time, and a status. the status would indicate if the resource has been requested (someone selected a date/time slot, but it has not been confirmed) or booked (if the trainer has reviewed and confirmed the reservation.) any resource reservation with either of those status values would not be available for selection.

 

if there is a preference for a particular resource or type of resource, you would get and apply a filter in the query that determines which resource id(s) you match in the resource schedule table and for just the date(s) you are trying to display. you would then join the rows from that table with the resource reservation table, using the resource id and date columns, to get the row(s) and therefore the start/end times the resource is (is not) available for selection. that should get you the data you need to display a grid of appointment slots that are (are not) available for selection.

[Barand]

You can do it with a minimal resource availability table but the difficulty lies with finding the times that are not there (ie the gaps between the bookings)

 

So if you have a bookings table

mysql> SELECT * FROM bookingscalendar
    -> ORDER BY bookingdate, start_time;
+----+-------------+---------+------------+----------+-------------+
| id | bookingdate | trainer | start_time | end_time | customer_id |
+----+-------------+---------+------------+----------+-------------+
|  1 | 2014-08-18  |       1 | 10:00:00   | 11:00:00 |         101 |
|  2 | 2014-08-18  |       1 | 13:00:00   | 14:30:00 |         102 |
|  3 | 2014-08-18  |       1 | 16:00:00   | 17:30:00 |         103 |
|  8 | 2014-08-19  |       1 | 09:00:00   | 10:30:00 |         106 |
|  4 | 2014-08-20  |       1 | 09:00:00   | 11:00:00 |         101 |
|  5 | 2014-08-20  |       1 | 12:00:00   | 13:00:00 |         105 |
|  6 | 2014-08-20  |       1 | 15:00:00   | 17:00:00 |         106 |
|  7 | 2014-08-21  |       1 | 10:00:00   | 11:00:00 |         102 |
+----+-------------+---------+------------+----------+-------------+

and a table for the trainer availability by day of the week (0 = Monday)

mysql> SELECT * FROM bookingavailability;
+-----------------+---------+------+-----------+------------+
| availability_id | trainer | day  | open_time | close_time |
+-----------------+---------+------+-----------+------------+
|               3 |       1 |    0 | 09:00:00  | 20:00:00   |
|               4 |       1 |    1 | 09:00:00  | 17:00:00   |
|               5 |       1 |    2 | 09:00:00  | 17:00:00   |
|               6 |       1 |    3 | 08:00:00  | 13:00:00   |
+-----------------+---------+------+-----------+------------+

you can now find those gaps. The first part of the query finds the time difference between a booking start_time and the end_time of the previous booking (or since the start of day if no previous booking that day). The second part of the query finds any gaps between the end of the last booking and that day's closing time.

mysql> SELECT trainer, day, bookingdate, from_time, to_time, timeslo
    -> FROM
    ->     (
    ->     SELECT a.trainer
    ->     , a.day
    ->     , bookingdate
    ->     , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,
 as timeslot
    ->     , IF(bookingdate=@prevdate,@prevend,open_time ) as from_t
    ->     , start_time as to_time
    ->     , @prevend := end_time as prevend
    ->     , @prevdate := bookingdate as prevdate
    ->     FROM bookingavailability a
    ->         JOIN (SELECT @prevend:=null,@prevdate:=null) as init
    ->         INNER JOIN bookingscalendar c
    ->             ON a.trainer = c.trainer
    ->             AND WEEKDAY(c.bookingdate) = a.day
    ->
    ->     UNION
    ->
    ->     SELECT a.trainer
    ->     , day
    ->     , bookingdate
    ->     , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) )
    ->     , IFNULL(MAX(end_time),open_time) as from_time
    ->     , close_time as to_time
    ->     , null as prevend
    ->     , null as prevdate
    ->     FROM bookingavailability a
    ->     LEFT JOIN bookingscalendar c
    ->         ON a.trainer = c.trainer
    ->         AND WEEKDAY(c.bookingdate) = a.day
    ->     GROUP BY a.trainer,day,bookingdate
    ->     ) as gaps
    -> WHERE timeslot > '00:00:00'
    -> ORDER BY trainer, day, bookingdate, from_time;
+---------+------+-------------+-----------+----------+----------+
| trainer | day  | bookingdate | from_time | to_time  | timeslot |
+---------+------+-------------+-----------+----------+----------+
|       1 |    0 | 2014-08-18  | 09:00:00  | 10:00:00 | 01:00:00 |
|       1 |    0 | 2014-08-18  | 11:00:00  | 13:00:00 | 02:00:00 |
|       1 |    0 | 2014-08-18  | 14:30:00  | 16:00:00 | 01:30:00 |
|       1 |    0 | 2014-08-18  | 17:30:00  | 20:00:00 | 02:30:00 |
|       1 |    1 | 2014-08-19  | 10:30:00  | 17:00:00 | 06:30:00 |
|       1 |    2 | 2014-08-20  | 11:00:00  | 12:00:00 | 01:00:00 |
|       1 |    2 | 2014-08-20  | 13:00:00  | 15:00:00 | 02:00:00 |
|       1 |    3 | 2014-08-21  | 08:00:00  | 10:00:00 | 02:00:00 |
|       1 |    3 | 2014-08-21  | 11:00:00  | 13:00:00 | 02:00:00 |
+---------+------+-------------+-----------+----------+----------+

 

[darkside619]

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:

 

TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,
as timeslot
-> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_t
-> , start_time as to_time
-> , @prevend := end_time as prevend
-> , @prevdate := bookingdate as prevdate

 

 

 

 

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.

[Barand]

First, my apologies - it looks as though I accidentally clipped a couple of the lines of code when I copied from the command line screen. Here is the correct query

SELECT trainer, day, bookingdate, from_time, to_time, timeslot 
FROM
    (
    SELECT a.trainer
    , a.day
    , bookingdate
    , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot
    , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time
    , start_time as to_time
    , @prevend := end_time as prevend
    , @prevdate := bookingdate as prevdate
    FROM bookingavailability a
        JOIN (SELECT @prevend:=null,@prevdate:=null) as init
        INNER JOIN bookingscalendar c 
            ON a.trainer = c.trainer
            AND WEEKDAY(c.bookingdate) = a.day
            
    UNION

      SELECT a.trainer
    , day
    , bookingdate
    , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot
    , IFNULL(MAX(end_time),open_time) as from_time
    , close_time as to_time
    , null as prevend
    , null as prevdate
    FROM bookingavailability a
    LEFT JOIN bookingscalendar c 
        ON a.trainer = c.trainer
        AND WEEKDAY(c.bookingdate) = a.day
    GROUP BY a.trainer,day,bookingdate
    ) as gaps
WHERE timeslot > '00:00:00'
ORDER BY trainer, day, bookingdate, from_time;

@prevdate and @prevend are user variables that used to store values from each row in the resultset so they can be used in the following row.

eg

in row 1 store the end_time of the booking in @prevend. (line 10)

In row2 the available gap between @prevend and the start_time of this booking is calculated (line 7)

 

However, if the second record is not the same date as the first then the available gap is the time between the start of day (open_time) and the start_time of the booking. Therefore the date of each record is stored in @prevdate (line 11).

    , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot
    , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time

Similarly in the second part (line 23), if there are no bookings for the day the available time at the end of the day is close_time minus open_time instead of the  end_time of last booking minus close_time

    , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot

[darkside619]

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

[darkside619]

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!

[darkside619]

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. 

[Barand]

When JOINing table it is not uncommon for the same column name to be used in both tables. In my tables (above) the "trainer" column is used in bookingscalendar and bookingavailabilty tables.

 

When referencing trainer, therefore, it is necessary to define which one otherwise you will get an "ambiguous column name" error. This is done by prefixing the column name with the table name

 

SELECT bookingavailability.trainer, bookingavailability.day ... FROM ....

 

Also, if you wanted to reference another database in your query then you would need to specify the database name too

 

SELECT myotherdatabasename.tablename.columnname , ... FROM ...

 

The writing and reading of the query is improved if you use table aliases

 

Thus

SELECT bookingavailability.trainer, bookingavailability.day, myotherdatabasename.tablename.columnname
FROM bookingavailability 
    INNER JOIN myotherdatabasename.tablename ON bookingavailability.trainer = myotherdatabasename.tablename.trainer
WHERE myotherdatabasename.tablename.trainer > 1
ORDER BY myotherdatabasename.tablename.trainer

becomes this if you use table aliases

SELECT a.trainer, a.day, t.columnname
FROM bookingavailability a
    INNER JOIN myotherdatabasename.tablename t 
        ON a.trainer = t.trainer
WHERE t.trainer > 1
ORDER BY t.trainer

http://en.wikipedia.org/wiki/Alias_%28SQL%29

[darkside619]

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?

[mac_gyver]

in the context of a monthly calendar, what do you want to display? displaying every open time slot for even one trainer (what if you have 20 trainers) would not be piratical.

 

your monthly calendar could at best show a clickable 'event' on the days that have available bookings (and a non-clickable, 'full' listing for days that have no open time slots), either just one event total, if any of the selected/filtered trainers have an opening, or one event for each selected/filtered trainer that has an opening on that date, with a hoover/pop-open tool or a link that gives you a view/page that consists of the booking grid with the open time slots for the clicked on date.

 

a monthly calender could be used for the appointment confirmation. you could display an 'event' on any days that have any un-confirmed appointment(s), for the currently logged in trainer. clicking on the 'event' would take that trainer to a grid of un-confirmed appointments that can then be reviewed and approved. assuming that a trainer would have the need to cancel an appointment, you would instead display an 'event' for all days that the trainer is available. clicking on any day would take the trainer to a grid that shows un-approved and approved appointments on that day with choices to approve/cancel each appointment.

[Barand]

Here is an example using the bookingscalendar table

<?php
include("db_inc.php");
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$sql = "SELECT 
          bookingdate
        , trainer
        , start_time
        , end_time
        , customer_id
        FROM bookingscalendar
        ORDER BY bookingdate,start_time";
$data = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $data[$row['bookingdate']][] = array_slice($row,1,4,true);
}
$json = json_encode($data);

//
// write to file
//
file_put_contents('mybookings.json', $json);

//
// show results (for demo purposes only)
//
echo '<pre>',print_r($data, true),'</pre>';
echo $json;


/**** RESULTS ***************************************************************

The array:
Array
(
    [2014-08-18] => Array
        (
            [0] => Array
                (
                    [trainer] => 1
                    [start_time] => 10:00:00
                    [end_time] => 11:00:00
                    [customer_id] => 101
                )

            [1] => Array
                (
                    [trainer] => 1
                    [start_time] => 13:00:00
                    [end_time] => 14:30:00
                    [customer_id] => 102
                )

            [2] => Array
                (
                    [trainer] => 1
                    [start_time] => 16:00:00
                    [end_time] => 17:30:00
                    [customer_id] => 103
                )

        )

    [2014-08-19] => Array
        (
            [0] => Array
                (
                    [trainer] => 1
                    [start_time] => 09:00:00
                    [end_time] => 10:30:00
                    [customer_id] => 106
                )

        )

    [2014-08-20] => Array
        (
            [0] => Array
                (
                    [trainer] => 1
                    [start_time] => 09:00:00
                    [end_time] => 11:00:00
                    [customer_id] => 101
                )

            [1] => Array
                (
                    [trainer] => 1
                    [start_time] => 12:00:00
                    [end_time] => 13:00:00
                    [customer_id] => 105
                )

            [2] => Array
                (
                    [trainer] => 1
                    [start_time] => 15:00:00
                    [end_time] => 17:00:00
                    [customer_id] => 106
                )

        )

    [2014-08-21] => Array
        (
            [0] => Array
                (
                    [trainer] => 1
                    [start_time] => 10:00:00
                    [end_time] => 11:00:00
                    [customer_id] => 102
                )

        )

)

The JSON-encoded array data:

{"2014-08-18":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"101"},
{"trainer":"1","start_time":"13:00:00","end_time":"14:30:00","customer_id":"102"},
{"trainer":"1","start_time":"16:00:00","end_time":"17:30:00","customer_id":"103"}],
"2014-08-19":[{"trainer":"1","start_time":"09:00:00","end_time":"10:30:00","customer_id":"106"}],
"2014-08-20":[{"trainer":"1","start_time":"09:00:00","end_time":"11:00:00","customer_id":"101"},
{"trainer":"1","start_time":"12:00:00","end_time":"13:00:00","customer_id":"105"},
{"trainer":"1","start_time":"15:00:00","end_time":"17:00:00","customer_id":"106"}],
"2014-08-21":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"102"}]}

*****************************************************************************/
?>

[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.

Edited August 23, 2014 by darkside619

[guido78]

Sorry to re-open this topic but I have found its content very useful.  I have setup a database along the same lines as what you created except I rename the day column in booking availability to dayofweek.  I have run this query:

SELECT trainer, trainername, dayofweek, bookingdate, from_time, to_time, timeslot 
FROM
    (
    SELECT a.trainer, a.trainername
    , a.dayofweek
    , bookingdate
    , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot
    , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time
    , start_time as to_time
    , @prevend := end_time as prevend
    , @prevdate := bookingdate as prevdate
    FROM bookingavailability a
        JOIN (SELECT @prevend:=null,@prevdate:=null) as init
        INNER JOIN bookingscalendar c 
            ON a.trainer = c.trainer
            AND WEEKDAY(c.bookingdate) = a.dayofweek
            
    UNION

      SELECT a.trainer, a.trainername
    , dayofweek
    , bookingdate
    , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot
    , IFNULL(MAX(end_time),open_time) as from_time
    , close_time as to_time
    , null as prevend
    , null as prevdate
    FROM bookingavailability a
    LEFT JOIN bookingscalendar c 
        ON a.trainer = c.trainer
        AND WEEKDAY(c.bookingdate) = a.dayofweek
    GROUP BY a.trainer,dayofweek,bookingdate
    ) as gaps
WHERE timeslot > '00:00:00'
ORDER BY trainer, dayofweek, bookingdate, from_time;

which looks like it is working but the bookingdate column is being returned as null values.  Any thoughts would be much appreciated.  Thanks

[Barand]

Booking date looks OK when I run it.

 

I had to comment out the trainername column, that should be in a trainer table, not in availability table)

mysql> SELECT trainer
    -> -- , trainername
    -> , dayofweek
    -> , bookingdate
    -> , CONCAT(from_time,'') as from_time
    -> , to_time, timeslot
    -> FROM
    ->     (
    ->     SELECT a.trainer
    ->  --   , a.trainername
    ->     , dayofweek
    ->     , bookingdate
    ->     , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time ))
 as timeslot
    ->     , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time
    ->     , start_time as to_time
    ->     , @prevend := end_time as prevend
    ->     , @prevdate := bookingdate as prevdate
    ->     FROM bookingavailability a
    ->         JOIN (SELECT @prevend:=null,@prevdate:=null) as init
    ->         INNER JOIN bookingscalendar c
    ->             ON a.trainer = c.trainer
    ->             AND WEEKDAY(c.bookingdate) = a.dayofweek
    ->
    ->     UNION
    ->
    ->       SELECT a.trainer
    ->  --     , a.trainername
    ->     , dayofweek
    ->     , bookingdate
    ->     , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot
    ->     , IFNULL(MAX(end_time),open_time) as from_time
    ->     , close_time as to_time
    ->     , null as prevend
    ->     , null as prevdate
    ->     FROM bookingavailability a
    ->     LEFT JOIN bookingscalendar c
    ->         ON a.trainer = c.trainer
    ->         AND WEEKDAY(c.bookingdate) = a.dayofweek
    ->     GROUP BY a.trainer,dayofweek,bookingdate
    ->     ) as gaps
    -> WHERE timeslot > '00:00:00'
    -> ORDER BY trainer, dayofweek, bookingdate, from_time;
+---------+-----------+-------------+-----------+----------+----------+
| trainer | dayofweek | bookingdate | from_time | to_time  | timeslot |
+---------+-----------+-------------+-----------+----------+----------+
|       1 |         0 | 2014-08-18  | 09:00:00  | 10:00:00 | 01:00:00 |
|       1 |         0 | 2014-08-18  | 11:00:00  | 13:00:00 | 02:00:00 |
|       1 |         0 | 2014-08-18  | 14:30:00  | 16:00:00 | 01:30:00 |
|       1 |         0 | 2014-08-18  | 17:30:00  | 20:00:00 | 02:30:00 |
|       1 |         1 | 2014-08-19  | 10:30:00  | 17:00:00 | 06:30:00 |
|       1 |         2 | 2014-08-20  | 11:00:00  | 12:00:00 | 01:00:00 |
|       1 |         2 | 2014-08-20  | 13:00:00  | 15:00:00 | 02:00:00 |
|       1 |         3 | 2014-08-21  | 08:00:00  | 10:00:00 | 02:00:00 |
|       1 |         3 | 2014-08-21  | 11:00:00  | 13:00:00 | 02:00:00 |
+---------+-----------+-------------+-----------+----------+----------+

Check your data

[guido78]

Thanks so much for getting back to me.  I have double checked everything and still get null values returned.  I've also restructured my tables to fit your examples.

 

Here are my tables:

+----+-------------+---------+------------+----------+-------------+------+-----------+
| id | bookingdate | trainer | start_time | end_time | customer_id | room | treatment |
+----+-------------+---------+------------+----------+-------------+------+-----------+
|  1 | 2015-08-24  |       2 | 15:00:00   | 16:00:00 | Mr Smith    |    1 |         1 |
|  2 | 2015-08-31  |       2 | 16:00:00   | 17:00:00 | Mr Jones    |    2 |         1 |
+----+-------------+---------+------------+----------+-------------+------+-----------+

With the following field types:

 

id = int

bookingdate =date

trainer = int

start_time = time

end_time = time

+-----------------+---------+-----+-----------+------------+-------------+
| availability_id | trainer | day | open_time | close_time | trainername |
+-----------------+---------+-----+-----------+------------+-------------+
|               4 |       1 |   2 | 09:00:00  | 17:00:00   | Lisa        |
|               6 |       1 |   4 | 09:00:00  | 17:00:00   | Lisa        |
|               7 |       1 |   5 | 09:00:00  | 17:00:00   | Lisa        |
+-----------------+---------+-----+-----------+------------+-------------+

availabilty_id = int

trainer = int

day = int

open_time = time

close_time = time

 

when i run this query:

SELECT trainer, day, bookingdate, from_time, to_time, timeslot 
FROM
    (
    SELECT a.trainer
    , a.day
    , bookingdate
    , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot
    , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time
    , start_time as to_time
    , @prevend := end_time as prevend
    , @prevdate := bookingdate as prevdate
    FROM bookingavailability a
        JOIN (SELECT @prevend:=null,@prevdate:=null) as init
        INNER JOIN bookingscalendar c 
            ON a.trainer = c.trainer
            AND WEEKDAY(c.bookingdate) = a.day
            
    UNION
 
      SELECT a.trainer
    , day
    , bookingdate
    , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot
    , IFNULL(MAX(end_time),open_time) as from_time
    , close_time as to_time
    , null as prevend
    , null as prevdate
    FROM bookingavailability a
    LEFT JOIN bookingscalendar c 
        ON a.trainer = c.trainer
        AND WEEKDAY(c.bookingdate) = a.day
    GROUP BY a.trainer,day,bookingdate
    ) as gaps
WHERE timeslot > '00:00:00'
ORDER BY trainer, day, bookingdate, from_time;

The data being returned is

+---------+-----+-------------+-----------+----------+----------+
| trainer | day | bookingdate | from_time | to_time  | timeslot |
+---------+-----+-------------+-----------+----------+----------+
|       1 |   2 | NULL        | 09:00:00  | 17:00:00 | 08:00:00 |
|       1 |   4 | NULL        | 09:00:00  | 17:00:00 | 08:00:00 |
|       1 |   5 | NULL        | 09:00:00  | 17:00:00 | 08:00:00 |
+---------+-----+-------------+-----------+----------+----------+

I must be missing something.  Perhaps the field typed ?

 

Thanks for your help !

[Barand]

These are my table definitions

CREATE TABLE `bookingscalendar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookingdate` date DEFAULT NULL,
  `trainer` int(11) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `bookingavailability` (
  `availability_id` int(11) NOT NULL AUTO_INCREMENT,
  `trainer` int(11) DEFAULT NULL,
  `dayofweek` int(11) DEFAULT NULL,
  `open_time` time DEFAULT NULL,
  `close_time` time DEFAULT NULL,
  PRIMARY KEY (`availability_id`)
)

[guido78]

That's very weird.  You have the same table structure as me.

CREATE TABLE `bookingscalendar` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bookingdate` date DEFAULT NULL,
  `trainer` int(11) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `customer_id` varchar(255) DEFAULT NULL,
  `room` int(11) DEFAULT NULL,
  `treatment` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `bookingavailability` (
  `availability_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `trainer` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `open_time` time DEFAULT NULL,
  `close_time` time DEFAULT NULL,
  `trainername` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`availability_id`),
  UNIQUE KEY `availability_id` (`availability_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[Barand]

I notice you have added another level of complexity with the inclusion of the "room" in the bookings.

 

So as well as checking for trainer availability you also have to check for room availability.

[guido78]

There are only 4 rooms available so I was just going to perform another nested query when displaying the results on the page to only show records that contain less than 4 bookings at the same time, unless there's a more efficient way of doing this in MySQL ?

[guido78]

Here's an SQL dump from my system.  Can't figure out why the bookingdate field is not being returned.

# Dump of table bookingavailability
# ------------------------------------------------------------

DROP TABLE IF EXISTS `bookingavailability`;

CREATE TABLE `bookingavailability` (
  `availability_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `trainer` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `open_time` time DEFAULT NULL,
  `close_time` time DEFAULT NULL,
  `trainername` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`availability_id`),
  UNIQUE KEY `availability_id` (`availability_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `bookingavailability` WRITE;
/*!40000 ALTER TABLE `bookingavailability` DISABLE KEYS */;

INSERT INTO `bookingavailability` (`availability_id`, `trainer`, `day`, `open_time`, `close_time`, `trainername`)
VALUES
	(4,1,2,'09:00:00','17:00:00','Lisa'),
	(6,1,4,'09:00:00','17:00:00','Lisa'),
	(7,1,5,'09:00:00','17:00:00','Lisa');

/*!40000 ALTER TABLE `bookingavailability` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table bookingscalendar
# ------------------------------------------------------------

DROP TABLE IF EXISTS `bookingscalendar`;

CREATE TABLE `bookingscalendar` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bookingdate` date DEFAULT NULL,
  `trainer` int(11) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `customer_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `bookingscalendar` WRITE;
/*!40000 ALTER TABLE `bookingscalendar` DISABLE KEYS */;

INSERT INTO `bookingscalendar` (`id`, `bookingdate`, `trainer`, `start_time`, `end_time`, `customer_id`)
VALUES
	(1,'2015-05-15',2,'15:00:00','16:00:00','Mr Smith'),
	(2,'2015-05-22',2,'16:00:00','17:00:00','Mr Jones');

/*!40000 ALTER TABLE `bookingscalendar` ENABLE KEYS */;
UNLOCK TABLES;

[Barand]

Your availability is for trainer 2, your bookings for trainer 1.

 

Dates will be null when no matching bookings for a trainer

[Barand]

There are only 4 rooms available so I was just going to perform another nested query when displaying the results on the page to only show records that contain less than 4 bookings at the same time, unless there's a more efficient way of doing this in MySQL ?

If you do that you won't know about the clients that are double-booked to inform them and change their bookings, you just hide the problem.

 

This query will pull any booking where a room is booked at the same time as another booking.

SELECT 
    id
   ,bookingdate
   ,room
   ,start_time
   ,end_time
   ,trainer
   ,customer_id
FROM bookingscalendar
WHERE id IN
    (
    SELECT
       b1.id
    FROM bookingscalendar b1
    INNER JOIN bookingscalendar b2
        ON b1.bookingdate = b2.bookingdate
        AND b1.room = b2.room
        AND b1.start_time < b2.end_time
        AND b1.end_time > b2.start_time
        AND b1.id <> b2.id
    )
ORDER BY
    bookingdate,room,start_time;

Ensure at the time of booking that only free rooms can be allocated.