PHP/MySQL help needed - Availability Calendar

[MarioRossi]

Hi there,

Please bear with me on this one as it is quite a hard problem to explain but I suspect with an easy solution (I just can't get my head around it)

I've started coding an availability calendar app and have the basics up and running. I can add and display entries fine with start and end dates etc.. My problem is I want to prevent double bookings and also search by availability.

At the moment I have 2 tables - 1 for property and 1 for bookings the structure of the bookings table is laid out as follows.

[table]
[tr]
[td]idBookings [/td][td]Int[/td]
[/tr]
[tr]
[td]idProperty [/td][td]Int[/td]
[/tr]
[tr]
[td]dateStart [/td][td]Date[/td]
[/tr]
[tr]
[td]dateEnd [/td][td]Date[/td]
[/tr]
[/table]

I am now struggling to work out programatically how to check availability. I think I have come up with one solution but it is extremely long winded and I fear overly-complicated.

What I was planning to do was as follows (in pseudo code in places to illustrate).

1. Get user to submit a form with the start and end date they would like.

2. On the form submitted page SELECT all the property LEFT JOIN the bookings table ON idProperty = propertyID

this would give me all the bookings for all the properties (Could be a lot of rows returned)

3. Loop through this recordset performing the following checks on each row (each row being a booking).

  3.1 $_POST['start'] !>= start AND $_POST['start'] !< end        <-- So here I'm checking that the requested start date is not during a booking
  3.2 $_POST['end'] !>= start AND $_POST['end'] !< end        <-- As above but checking end date is not during a booking
  3.3 $_POST['start'] !<= start AND $_POST['end'] !>= end    <-- Here I'm checking the new requested booking doesn't straddle an existing one

If I'm right then if all three of those conditions are met then the property is available for the requested period. Can anybody suggest a better way to do things as I don't want to implement anything that is overly bloated when a neater solution may exist.

That code run on a busy site I would imagine could be a bit of a nightmare as say there are 1000 properties all with 10 bookings that's 10,000 rows returned to PHP straight away and then I have to loop through and do my checks.

The problem would get worse if i wanted to use it as a sitewide availability check as I would return duplicate property entries for each booking that didn't conflict

I hope somebody can help me with this because I can't get my head around it.

Thanks in advance

- Andy

[Barand]

Does this help

http://www.phpfreaks.com/forums/index.php/topic,99163.15.html

[MarioRossi]

It would appear so, Thank you very much I'm going to study that carefully.

- Andy

[Barand]

There are 4 situations to check for a double booking
[pre]
    current        current
    booked          booked
      start            end
     
        |              |
  S--------E          |            (requested Start and End)
        |              |
        |        S--------------E
        |              |
        |  S-------E  |
        |              |
  S------------------------E
        |              |[/pre]

[MarioRossi]

Yeah thats how I got to my original plan - I was thinking backwards though, I was going to return all the rows then use php to check when I should really be doing the check in the MySQL query

We can also disregard the 3rd situation there as the first two will perform that check already i.e. for the 3rd situation to be true either the 1st or 2nd must also be true.

Thanks again I think I have a plan of attack again. I may need help with selecting distinct rows though at some point.

[MarioRossi]

Just out of interest is it possible to do the following?

SELECT * FROM (PropertyTable LEFT JOIN BookingsTable ON b.arrive NOT BETWEEN '$d1' AND '$d2' AND b.depart NOT BETWEEN '$d1' AND '$d2') ?? 

[Barand]

So long as you define the table aliases

... PropertyTable p  LEFT JOIN BookingsTable b ......

and you would have to include the property id in the join condition

[MarioRossi]

This is now getting so close.

My query runs fine in all circumstances without any MySQL errors but it only works for the first booking in the bookings table. So say I have the following two rows in the bookings table only the first will be validated against.

ID      Start              End
1      2006-09-11      2006-09-18
2      2006-10-18      2006-10-27

Could you please look through this query to see where i'm going wrong? It's quite a biggy, I've left out the Group BY as I know the problem is not in there and I've also split my final join for clarity as I believe this is where I am going wrong.

I realise it's quite a bit to look through so I would be extremely grateful

Thanks Again

SELECT Villa_Property.idVilla_Property, min(Villa_Images.Image)  AS DisplayImage, Villa_Property.Title, Villa_Property.idOwner, Villa_Property.Sleeps, Villa_Property.Pool, Villa_Property.Parking, Villa_Property.TV, Villa_Property.PrivateGarden, Villa_Property.Internet, Villa_Property.Description, Villa_Property.Email, Villa_Property.DateAdded, Villa_Property.idPropertyType, Villa_Property.Cooker, Villa_Property.Fridge, Villa_Property.Freezer, Villa_Property.WashingMachine, Villa_Property.idLocation, Villa_Property.Safe, Villa_Property.Bath, Villa_Property.Shower, Villa_Property.DrinkingWater, Villa_Property.BabyChanging, Villa_Property.Phone, Villa_PropertyTypes.Name, Villa_Locations.idParent, Villa_Locations.idVilla_Locations, Villa_Locations.LocationName FROM ((((Villa_Property LEFT JOIN Villa_PropertyTypes ON Villa_PropertyTypes.idVilla_PropertyTypes=Villa_Property.idPropertyType) LEFT JOIN Villa_Locations ON Villa_Locations.idVilla_Locations=Villa_Property.idLocation) LEFT JOIN Villa_Images ON Villa_Images.idVilla_Property=Villa_Property.idVilla_Property)";


$query_PropertySearchQuery.= "INNER JOIN Villa_Bookings ON ('$startdate' NOT BETWEEN Villa_Bookings.dateStart AND Villa_Bookings.dateEnd) AND ('$enddate' NOT BETWEEN Villa_Bookings.dateStart AND Villa_Bookings.dateEnd) AND NOT (('$startdate' < Villa_Bookings.dateStart) AND ('$enddate' >= Villa_Bookings.dateEnd)) AND Villa_Property.idVilla_Property = Villa_Bookings.idVilla_Bookings";


$query_PropertySearchQuery.= ") WHERE Villa_Property.Active=1";

[Barand]

Hard to tell by looking at with nothing to run it against. However a couple of observations.

You LEFT JOIN to property_type and location tables. It seems odd from a referential integrety point of view that you would have property on file that is of a type not on file and in a location not on file.

As you are now INNER JOINing to the bookings table then move the date conditions to the WHERE clause instead of the ON clause, just leaving the id bits in the ON clause

[MarioRossi]

You are right the joins were getting a bit out of hand so decided to get the books out and perform my first ever sub select :o working a treat now. Had to throw DATE_ADD() and DATE_SUB() into the mix as well to account for the fact that an arrival date could be the same as a departure date.

Thanks so much for your help there's a site donation winging its way over now.