Ok, here goes. Pay attention, I'll be asking questions afterwards. 🙂
Step one:
What do you need to know about a reservation?
You need the name of who made the reservation,
the name or ID of the item that is being reserved,
the start date and time of the reservation,
the end date and time of the reservation.
That you be a table like
username varchar(255)
itemid int
startdate datetime
enddate datetime
The first reservation is easy, just gather the data and stick it into the database.
The second reservation can only be made if there are no conflicting reservations.
That means that the reservation periods may not overlap.
But how do you check if they overlap?
The only thing we know about the reservation period is the beginning and the end.
But that's more than enough, becuase if two periods overlap, that means that one period starts or stops while the othercontinues.
There are four ways of overlapping
A falls inside B
-----------AAAAAAA-------
--------BBBBBBBBBBBBBBB--
or B begins during A
-----------AAAAAAA------
--------------BBBBBBBBBBB-
or A begins during B
-----------AAAAAAA------
--------BBBBBBB----------
or B falls inside A
-----------AAAAAAA------
-------------BBB----------
So, in order to know if there is a conflict,
you need to check wether any of these conflicts exist.
So we need a list of conditions that define an overlap
A falls inside B
Both the start and end date of A fall between the start and end date of B
B begins during A
The start of B falls between the start and end of A
A begins during B
The start of A falls between the start and end of B
B falls inside A
The start and end of B fall between the start and end of A
Condense that down a little and it means:
Neither the start nor end of A may fall between the start and end of B,
and neither the start nor end of B may fall between the start and end of A.
In pseudo SQL that would translate to something like:
SELECT *
FROM B
WHERE start > $a_start AND start < $a_end
OR end > $a_start AND end < $a_end
OR $a_start > start AND $a_start < end
OR $a_end > start AND $a_end < end;
If there are any matches on this query, it means that overlapping records exist, and the new reservation should be denied.
