Jump to content


Photo

PHP/MySQL help needed - Availability Calendar


  • Please log in to reply
9 replies to this topic

#1 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 06 September 2006 - 10:22 PM

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.














idBookings Int
idProperty Int
dateStart Date
dateEnd Date


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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 11:50 PM

Does this help

http://www.phpfreaks...c,99163.15.html
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 06 September 2006 - 11:53 PM

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

- Andy

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 11:59 PM

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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 07 September 2006 - 12:11 AM

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.

#6 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 07 September 2006 - 12:21 AM

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') ?? 

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 07 September 2006 - 12:34 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 07 September 2006 - 10:08 PM

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";

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 07 September 2006 - 10:35 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 08 September 2006 - 01:41 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users