Hello Everyone,
I have a bit of a problem, and I'm hoping someone out there might be able to give me some direction.
I am in the process of setting up a site to allow bookings of holiday cottages. It shows a nice calendar with all the days booked so far shaded, and the user has a form to specify when they would like to book. All fine so far.
My problem comes because I would like to have a 'Show next available booking date' link or allow people to search for cottages free between certain dates. Obviously I can use SQL on my database (mysql) to check if a date is booked, but not to check if a date is not booked. Also bookings can be anything from a day to a year.
Now I need a way around this. I'm willing to consider anything, redesign of database, large array solutions etc etc.
Currently my database has a bookings table with startdate and enddate fields. Most of the cottages are booked on a week to week basis so Saturday to Saturday or Friday to Friday, but this is not always the case (although if it made my life easier I could force this).
I have thought of a number of solutions.
Taking the end date of one booking and the start date of the next and enumerating all days inbetween . This might work if I ensured no overlap in bookings.
Booking weekly, thereby having a table with 52 fields indicating each week in the year, then I can convert a date to its week, and see whether it is available or not (any ideas on how efficient this is).
Filling an array of 365 values with each day that is booked, then enumerating through the array and see whats not booked.
Some of these solutions are far from optimal I know, but my head is in such a mess at this stage I cant think straight.
Any comments would be appreciated.
Toby Allen.
