I'm building a system which allows an administrator to add resources which can be sold in time slots. The variables are start time, end time and interval (x minutes). A front end user will go to the site, select a resource and a date and view the available slots.
I can easily generate an array of possible slots be looping from start time to end time in x minute intervals.
Where it gets tricky for me is excluding some times from the available slots. There are the standard bookings - 1 slot at a time, but there will also be periods when the resource will be unavailable, so one booking could take up 10 time slots.
My thinking was to first generate the array of possible slots, then query the DB, removing time slots that have already been booked. This would involve splitting each longer booking into chunks.
I'm wondering if there's a pure MYSQL way to do this or at least make it as efficient and flexible as possible.