PHP MySQL – How to Approach Complex Queries

algorithmsMySQLPHPrules-and-constraintsscheduling

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?

Best Answer

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)
GROUP BY 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!

Related Topic