guido78

Thank you so much Barand. Can the original query be modified to show timeslots even if no bookings have been made for a day ? I've restricted the query to only show available time slots for a given date, as: SELECT trainer, trainername, day, bookingdate, from_time, to_time, timeslot FROM ( SELECT a.trainer, trainername , 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, trainername , 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' and bookingdate = '2015-06-03' ORDER BY trainer, day, bookingdate, from_time; So, if a customer is wishing to book an appointment for today, at the moment it will only produce a result if there is a valid entry in bookings availability table. However if no bookings have been made for today then really there should be a time slot from the start_time to end_time of the bookings calendar entry. Is this possible ?

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;

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 ?

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;

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 !

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