Reservation system renting a block of a day

Question

I am having trouble validating days with a reservation system I have to build (PHP/Mysql). I posted a similar question earlier Reservation system dates But something changed.

The problem I am having; We have a studio/conference room available at office. This is up for rent on a limited time scale. The user select on what day,time and how many hours he will want to rent the room.

Reservation step by step:

1- The user selects the month in which he wants to rent the studio. 2- The user selects the day on which he wants to rent the studio. (here i get stuck) 3- The user selects the time on which he wishes to get the key.

Time on which the key is receivable:

00:00:00 - 06:00:00
06:00:00 - 12:00:00
12:00:00 - 18:00:00
18:00:00 - 24:00:00

These times are the times, in which the user is will receive the key for the door.

4- The user selects the amount of hours he wishes to rent the studio.

The problem: I am able to check on what days the studio is rented but not exactly what time. I know how to check on time in PHP. But for example;

User 1 rents the studio on 27th April 2012 from 12:00:00 until 18 hours later. User 2 wants to rent the studio on 26th April 2012 from 18:00:00 until 12 hours later. User 3 wants to rent the studio on 28th April.

How would I be able to check if User 2 can rent the studio until max 27th april 12:00:00. And how is it possible to check if User 3 can rent the studio on the 28th and make sure it is available after 06:00 .

Database:

id                 int(11)
user_id             int(11)
rental_name         varchar(255)
key_receive_time varchar(255)
start_date         int(11)
end_date         int(11)
total_hours      int(11)

I have been at this for 2 days in a row now and still can't crack it. I suppose the solution is simple but I probably search in the wrong direction.

Thanks in advance.

EDIT example queryie:

SELECT * FROM reservations
WHERE (start_date >= " . $start_block_1 . " AND end_date <= " . $end_block_1   . ")
OR    (start_date >= " . $end_block_1   . " AND end_date <= " . $start_block_1 . ")

The $start_block_1 and $end_block_1 represent the timestamp 00:00:00 - 06:00:00 blocks of a day .

I perform this query 4 times for there are 4 blocks in the day on which the user can pickup the key.

Answer 1

Seems like you need to check if an interval -- a pair of start and end date -- conflicts with existing intervals in your database. If this is what you want then here is the query:

-- //// FOR TESTING \\\\
CREATE TABLE reservations (start_date INT, end_date INT);

INSERT INTO reservations(
    start_date,
    end_date
)
VALUES (
    UNIX_TIMESTAMP('2012-04-27 12:00:00'), -- start date booked by user 1
    UNIX_TIMESTAMP('2012-04-28 06:00:00')  -- end date booked by user 1
);

SET @d1 = UNIX_TIMESTAMP('2012-04-26 18:00:00'); -- start date requested by user 2
SET @d2 = UNIX_TIMESTAMP('2012-04-27 06:00:00'); -- end date requested by user 2
-- \\\\ FOR TESTING ////

SELECT 1
FROM reservations
WHERE
(@d1        <= start_date AND start_date <  @d2     ) OR -- start datetime lies "inside" the interval
(@d1        <  end_date   AND end_date   <= @d2     ) OR -- end datetime lies "inside" the interval
(start_date <= @d1        AND @d2        <= end_date)    -- the interval itself lies inside start and end datetime

The query will return the rows that conflict with the given interval. The variables @d1 and @d2 are for testing your queries in a mysql client; replace them with data passed in from PHP code.

Note: the comparison operators < and <= might need to be changed depending on whether your start/end datetimes are inclusive or exclusive.