I have a reservation system that I have coded and my final task is to allow the user to enter a set of dates and then show them all available options.

The reservation table is set up with unit_id, check_in and check_out. There are twelve different unit numbers.

So I have to somehow iterate through them and find the units that have no taken dates during the date range the user has put in. Just having some trouble conceptualizing what needs to happen.

Pseudo code

$units = array (all the units);

foreach($units as $key = $value){

    Run a query searching through all reservations for that unit, 
    if the unit is available, add to our $available_units array for the view later


Checking availability has me stumped too though. What is the most efficient way to use the customers available date range and compare to the date ranges in the db?

  • Quick clarification.. what do you mean by all available options? For examples: unit's 1, 2, 3 have spare dates on 24/4/2012, 25/4/2012, 26/4/2012 respectively. Im searching for 24/4~26/4. Do you want it to say: a.) no units available. b.) unit 1 available for 24/4, unit 2 has 25/4, unit 3 has 26/4?
    – rks
    Commented Apr 28, 2012 at 19:03
  • Ah yes, so by "all available options" I simply mean, of the set of units, which ones are not occupied on the users desired date range. If any day of the range is taken, we presume the unit is not available for the users desired range. This is a free add on I am doing for client, so it doesn't have to be amazing, just a nice utility to browse what units have availability for the given date range.
    – absentx
    Commented Apr 28, 2012 at 19:56

3 Answers 3


I come across this kind of requirement all the time. Let's say you have a date range you want to check (ie, find free units for) which is defined with a start and an end date. In the database, for the sake of making it clear what we're talking about, lets say existing reservations have a first and a last date (of the reservation).

So there's six possible cases for any given reservation (|xxxx|) when compared to the date range you're checking ([----]):

  1. [--|xxxx|--] It can be entirely contained within the check range. This means the date range is not available for this unit.
  2. |xx[xx|----] It can straddle the start of the check range. Again, this unit is not available.
  3. [----|xx]xx| It can straddle the end of the check range. Not available.
  4. |xx[----]xx| It can completely contain the check range. Again, not available.
  5. |xxxx|[----] It can preceed the check range. This unit may be available!
  6. [----]|xxxx| It can entirely follow the check range. This unit may be available!

You'll notice that in all the cases where the unit is not available, the first date is always before* the end date, AND the last date is always after* the start date. This is not true for the two cases where the ranges don't overlap. So! We have a distinguishing condition.

(* or equal to)

In this situation, units that are in use will have at least one existing reservation where first <= 'end' AND last >= 'start'. MySQL has a bunch of really nice grouping and aggregation functions we can use here to actually get MySQL to do all the heavy lifting for us. For instance, we could do something like this:

SELECT Units.*, SUM(IF(ReservationID IS NULL, 0, First <= 'end' AND Last >= 'start')) AS ConflictingReservations
FROM Units LEFT JOIN Reservations USING (UnitID)
HAVING ConflictingReservations = 0

In the above query (which I admit I haven't actually tested cough, but the idea is sound, honest!) we left join the Units table to the Reservations table so that if a unit has never been reserved it'll still show in the results. From there, we group the rows by the UnitID and create a derived column called "ConflictingReservations" which contains the sum of a logical comparison. As mentioned previously, if that logical comparison is TRUE (which in MySQL has the value '1') it means the specific reservation we're checking overlaps the check range. Summing all the "hits" will give us the number of reservations for a particular unit that overlap with the check range.

Note: we guard the logical comparison against the case where a unit has NEVER been booked before and therefore the left join returns a row with a NULL values in the First and Last columns. If the ReservationID is NULL (and therefore the First and Last dates would be null, forcing the entire ConflictingReservations derived column to NULL rather than 0) we count that as a non-match/false, ie: 0.

The final step says "throw away all rows (ie, units) that have a nonzero number of conflicting reservations." This should leave you with a list of rows from the Units table that have no reservations within the check range (ie, that are available to book in that range.)

If I've managed to get edge cases wrong (as I did with the zero reservations case) or syntax wrong etc, let me know and I'll edit the answer!

  • Re the "or equal to" asterisk - this is something you will need to decide upon as per your data types and requirements. If a commencing reservation is permitted to start at the exact same date or datetime as a reservation that's just ending, remove the "or equals" part from the checks. This is probably more important if you're just comparing "date" types, rather than "datetime" with checkin and checkout times included, since you'll want some time in between to clean the rooms etc :) Commented Sep 4, 2012 at 1:48
  • wow, two and a half years and no one spotted the bug in the query... i'll update the answer to fix it. Commented Oct 27, 2014 at 2:23

Lets assume that the customer wants the date range date_from to date_to, where date_from <= date_to. You told us that you have a reservation table, let's call it reservation_table. Using the column names you provided, i.e. reservation_table = unit_id, check_in, check_out, such that check_in <= check_out

How about the following query in mysql?

select r.unit_id from reservation_table r
  where r.check_in > date_to  OR r.check_out < date_from;

I built a reservation system a while ago and faced a similar problem. It was slightly different in that I had a fixed number of spots (units) to fill (they weren't indexed).

My solution was similar to the answer posted by @rrufai, except that in my case that query would give me the reservations for that date range, not the availability.

I created a MySQL function that performed a query that retrieved the number of reservations for a given day, and returned that number. From there that was subtracted from the number of spots available in total, giving me the available spots. In your case your function could return a list of taken units, then from that work out what ones are free (i.e. not in that list).

I reduced it to looking per day (so I would run the function for n days the user was searching for, although I imagine you could do the whole thing in a single function/procedure.

  • If each "unit" could take multiple bookings (which would be the case if a "unit" was actually a class of unit, not a specific room) then yes, the query I mentioned above could be adapted to change the ExistingReservations field to a RemainingRooms field by subtracting the existing field from UnitClass.TotalRooms (for example) and then changing the HAVING block to "HAVING RemainingRooms > 0". It's a very flexible query approach. Commented May 8, 2012 at 4:18
  • Thats a very nice solution you posted. I hadn't thought of doing it that way.
    – phindmarsh
    Commented May 8, 2012 at 21:02
  • My edit above changes this note - it's actually more complex than described. I'm working on a solution to basically exactly that problem in another question (again, true to form I'm asking it years later, but still!) link Commented Oct 27, 2014 at 4:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.