Simple availability check / booking sys

PHP
1

Hello fellow Sitepointers :slight_smile:

I was wondering if some of you might lend me some advice.
I’ve just started coding what I want to be a fairly simple availability check & booking system. I have hit a few queries I need to run by, both at the database design/layout end and also the actual process end. :blush:

I’ll plunge straight in:

  1. How might it be best that I lay my bookings table out, to allow the system to know what availability there is, when running through a check for a visitor using the website’s frontend?
    Currently in the “bookings” table on the DB, I have got “arrival”, “departure” and “number_of_nights” as fields. My worry is that, if there’s a booking for say 7 nights starting 1st Jan and departing 8th Jan, how will the system not show anything other than full availability if a visitor, for instance, wanted to search for a stay from the 5th Jan. As the date “yyyy-01-05” won’t actually be specified in the DB, using the DB design I’ve got so far.

  2. How would I generate a year-view calendar, with the current month (eg: October 2010) as the first month rather than January of the current year (which wouldn’t be relevant), with each day “clickable” (very much like in this example: http://style-vs-substance.com/projects/calendar/example_full_year.php).

The idea will be that, once the visitor has selected a particular day, they’ll be able to see how much availability there is at the site for that day and then click on a link at the top of the page, which will take them to a form to fill-in their party size, how many tent spaces they want, etc.

  1. I’d really appreciate some help with writing the code that will check the database, once the availability search form has been submitted, which will then result - from a visitor’s point of view - in one of the following:

    a) a popup saying that the booking can’t be met (so, for instance, this might happen when there might be enough space on the day that they want to arrive, but half way through their proposed stay, the site is fully booked)

    b) a confirmation message saying that the booking can be met, showing the total price (just a case of multiplying the number of adults, children, dogs and “electric hook-ups” by the prices contained within a separate database table) and inviting the visitor to fill-in their personal details and proceed to pay a 10% deposit via paypal payments pro.

There are some more queries, including some backend “admin” functionalities I’d like to include, but these three are the fundamentals that I would be very much appreciative of some help with, for the moment.

Of course, if anyone on here would prefer to talk privately and do this as a paid-for consultation, I’m open to ideas/offers and keen to strike up useful coding-partnership-type relationships, so do drop me a PM. I hope this is within the forum rules - I have looked but nothing was immediately apparent - so do let me know if not.

Many thanks in advance guys. :slight_smile:

Al

2

This is where you can use MySQL BETWEEN.

In a nutshell,

SELECT COUNT(*) FROM bookings WHERE 
('$start_date' BETWEEN start_date AND finish_date) OR ('$finish_date' BETWEEN start_date AND finish_date) OR ('$start_date' > start_date AND '$finish_date' < finish_date)

Then, to show the result,


if ($count > 0) // there was a clash
{
    $query = mysql_query... // loop the clashes and display the details
} else {
    echo 'no clashes found';
    // insert new booking
}