Jump to content

Archived

This topic is now archived and is closed to further replies.

MarioRossi

PHP/MySQL help needed - Availability Calendar

Recommended Posts

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

Share this post


Link to post
Share on other sites
Does this help

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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') ?? 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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";

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.