Availability Calendar

I did something much more simple than what you are aiming for using Google Calendar. The client wanted a way for potential customers to see if they were busy before making appointments.

GCal provides ical files for any calendars that they have, so when a customer enters a date, I use curl to get the ical file from google, then grep it for the date. This has worked well, and gives the client a well designed interface for adding things.

If that wouldn't work for you, I'd do it like this.

Have a database only containing bookings. If it's not booked then it's not in there. This accounts for leap years etc - no-one is going to have a booking for the 45th of February for instance.

To display the dates, my logic would be

loop through the dates:
    is the date booked:
        yes -> show as booked, make it a link or whatever
        no -> show white

You can get the dates using the built in functions of whatever language you are in.

I'd use MYSQL as the database, as it's easier to use with my languages of choice (PHP, Python etc).

My database for bookings would have the columns:

id, date, building_number, customer_id

Then a database of customers:

customer_id, name, address, telephone, invoice_date, paid

etc etc.

Then I'd do a join on the two tables if I needed to know a combination of data from both tables.

Easy!

OK, I am kind of half with you here. I can see that having a db of bookings only might well work but I am struggling to see how I 'loop through the dates', if the dates are not there. I can see if the date is there fine, if it is not how am I going to code the page to show the dates in the first place, ie. How do I show there are 31 days in January for instance.

I guess I could have have for instance for the 1st of jan.

if 01/01/2009 is in DB then show 1 in red else show 1 in blue

But it appears to me doing it this way would mean I need up to 366 if statements on my page.

No - just rely on the language you use to know if days exist.

In PHP, I'd either use a combo of mktime and date, or cal_days_in_month.

Something like this:

<?php
    $year = 2009;

    for($month=1; $month<=12; $month++;) {

        $days_in_month =  cal_days_in_month(CAL_GREGORIAN, $month, $year);

        for($day=1;$day<=$days_in_month;$day++;){
               echo $day + '/' + $month + '/' + $year;
        }

     }


?>

That isn't tested or anything, just written off the top of my head. It loops through 12 months, then works out how many days are in the month, then loops through those and prints them to the screen.

In your case, instead of just an echo, you'd use if to check if it was booked or not, then format it as needed.

Ouch, that looks complicated - will look in to the equivalent in asp.

Well, it's only 7 lines of code, of which two are just a } and one that tells you the year is 2009. I'd argue that it's pretty much the simplest code that you could write do do this.

redacted