Finding Free Blocks of Time in MySQL and PHP

PHP free time blocks from start and end dates

They way I would work through it would be to try thinking of the problem like you are carving something out of a material. I'd start with a day full of free time [09 - 17] then loop over the appointments. For every appointment you are either splitting the existing free time (then re-ordering by start date) or changing the range. It's a lot easier if the times don't overlap. If the appointment is outside of the free time range you can ignore it.

{----}  {--------} Free Time Range
           [---]   Appointment in the middle - you need to split

{----}  {--------} Free Time Range
        [---]      Appointment at the beginning - change the start date

{----}  {--------} Free Time Range
             [---] Appointment at the end - change the end date

{----}  {--------} Free Time Range
        [--------] Appointment fills the whole spot - delete

How to check for blocks of availability in a MySQL booking database?

Here is a way to do this in SQL, for just two-day bookings, for room_1:

SELECT
    avail1.date start_date,
    "room_1" room_name
FROM vg_booking_availability avail1
/* This row is the next day to the first one */
INNER JOIN vg_booking_availability avail2 ON (avail1.date + 1 = avail2.date)
WHERE
    avail1.room_1 = 0
    AND avail2.room_1 = 0
    /* Add in a clause to check future dates only here */
;

You could add all the rooms in this as bracketed OR statements, but I'd be inclined to run that as a separate query (otherwise you'd have to re-search your result in PHP to determine which room returned as available)

We are getting into a bit of trouble here because all the rooms are denormalised - they would be better in another table where they can be treated much more generically.

This example can be expanded by adding more aliased rows for 4-day and 7-day searches respectively. The unavailability of rooms on a Thursday (or whatever other rules) is not directly relevant to the problem, since you can just create future rows of availability (based on how far into the future people book) and then make rooms unavailable according to those rules. That's a separate (and trivial) problem.

I'd also be inclined to change this, so you use NULL as available and a foreign key to a customer table as unavailable. That will then give you useful information about why a room is unavailable, and will allow you to make it available again easily if a specific customer cancels their booking.

Lastly, this solution has the capacity for a large number of joins, and so testing this against data sets is essential. If you were to run this on a 10K row table it'd be fine, but it might not be if you have 1M rows (depending on your hardware and load, of course). So, once you have this working, I recommend you create a data generator (PHP is good for this) and ensure you can get the performance you require.

Finding empty time blocks between two dates?

I did this a while ago. We had a similar structure:

Available (contained all working hours for an employee, flexible working hours)
Appointments (similar to yours)

What I did was basically this (steps):

Get all start and end datetimes for employee < x >, sorted by startdate
let startAvailable = start of the time search (in your case 11/9/2010 @ 8am)
let appointment = first appointment in the list of appointments
get the startdate of the first appointment. If the difference between these is big enough, there's your block
if not, let startAvailable = enddate of appointment
remove appointment from the list, let appointment be the next appointment
repeat the process of checking for an available block

MySQL availability calendar - way to calculate free dates?

Understanding your question as Retrieve all 7 day interval blocks of the current and next year whose ranges do not overlap any interval blocks already existing in the available_dates table:

To work with all days of the current and next year, we have to create a separate table (yeardates) containing DATEs of all days of the current and next year. This will facilitate our OUTER JOIN operation in the retrieval query.

Code to define the yeardates table and insert dates:

CREATE TABLE yeardates 
(
    yeardate DATE NOT NULL,
    PRIMARY KEY (yeardate)
) ENGINE = MyISAM;

DELIMITER $$
CREATE PROCEDURE PopulateYear(IN inputyear INT)
    BEGIN
        DECLARE i INT;
        DECLARE i_end INT;
        SET i = 1;
        SET i_end = CASE WHEN inputyear % 4 THEN 365 ELSE 366 END;
        START TRANSACTION;
        WHILE i <= i_end DO
            INSERT INTO yeardates VALUES (MAKEDATE(inputyear, i));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL PopulateYear(2012);
CALL PopulateYear(2013);

The table is then created and contains all days of the current and next year. If we ever need to insert days for subsequent years, just CALL the procedure again with the year as the parameter (e.g. 2014, 2015, etc..).

Then we can get the 7-day blocks that don't overlap blocks in the available_dates table:

SELECT
    a.yeardate AS blockstart,
    DATE_ADD(a.yeardate, INTERVAL 7 DAY) AS blockend
FROM
    yeardates a
LEFT JOIN 
    available_dates b ON 
        (a.yeardate BETWEEN b.start_date AND b.end_date)
        OR
        (DATE_ADD(a.yeardate, INTERVAL 7 DAY) BETWEEN b.start_date AND b.end_date)
WHERE
    b.available_id IS NULL

That retrieves all free 7-day blocks based on the bookings of all properties, but if we need to get the free 7-day blocks for just a particular property, we can use:

SELECT
    a.yeardate AS blockstart,
    DATE_ADD(a.yeardate, INTERVAL 7 DAY) AS blockend
FROM
    yeardates a
LEFT JOIN 
    (
        SELECT *
        FROM available_dates
        WHERE property_id = <property_id here>
    ) b ON 
        (a.yeardate BETWEEN b.start_date AND b.end_date)
        OR
        (DATE_ADD(a.yeardate, INTERVAL 7 DAY) BETWEEN b.start_date AND b.end_date)
WHERE
    b.available_id IS NULL

Where <property_id here> is the property_id. We can even do the selection based on multiple properties at a time by simply changing it to WHERE property_id IN (<comma sep'd list of property_ids here>).

Show closest result to current date & time (MySQL & PHP)

I would ditch using the PHP date/time methods and rely on MySQL giving a query that looks like

SELECT * FROM table_name 
WHERE date > CURRENT_DATE
OR (
    date = CURRENT_DATE
    AND 
    time > CURRENT_TIME
)
ORDER BY date ASC, time ASC LIMIT 1

The OR makes sure that it gets the correct records else the TIME portion would block i.e. a result at 03:00 from the next day from appearing if the current time was at 06:00

I see you are using timestamp values there so you can always still pass in the PHP date numeric in place of CURRENT_DATE. This would give a final script of

$timestamp_now = strtotime(date('d-m-Y')); //Creates a timestamp of the currect date
$sql = mysql_query('SELECT * FROM table_name 
                    WHERE date > '.$timestamp_now.'
                    OR (
                        date = '.$timestamp_now.'
                        AND 
                        time > CURRENT_TIME
                    )
                    ORDER BY date ASC, time ASC LIMIT 1') or die(mysql_error());
$data = mysql_fetch_array($sql);

I would advise considering changing the database if possible to store just as a MySQL DATETIME field, as then you can change this query to simply WHERE datetime > NOW(), but that's entirely up to you. Just have always found MySQL date handling more logical than PHPs.