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.
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