Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  a1210 Apr 28 '12 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 Apr 28 '12 at 19:56

3 Answers 3

up vote 2 down vote accepted

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(First <= 'end' AND Last >= 'start') AS ExistingReservations
FROM Units LEFT JOIN Reservations USING (UnitID)
GROUP BY UnitID
HAVING ExistingReservations = 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 "ExistingReservations" 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.

The final step says "throw away all rows (ie, units) that have a nonzero number of conflicting existing 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 or syntax wrong etc, let me know and I'll edit the answer!

share|improve this 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 :) –  Jon Kloske Sep 4 '12 at 1:48

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;
share|improve this answer

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.

share|improve this answer
    
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. –  Jon Kloske May 8 '12 at 4:18
    
Thats a very nice solution you posted. I hadn't thought of doing it that way. –  phindmarsh May 8 '12 at 21:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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