writing and searching calendar date with mysql

[derksj]

Hi i am trying to write an appartment booking script

I have this form with two text fields with a date in it
and write this to a database

for example:

my appartment is booked for 01-01-2005 till 01-02-2005

how can someone search the database for dates that are booked or not booked

so if someone searches for 01-01-2005 till 01-02-2005 or parts of this period then the text "appartment is booked" must appear
else the text "appatment is free" must appair

what should the code look like for writing the dates to the database and what should te code look like for searching the database

[travelbuff]

I am working on a similiar problem.

First, remember that MySql stores date in a 'date' column formatted yyyy-mm-dd, so if you are going to display in on your page in another format you will need to use date and strtotime

Here is an example from my project:

date('Y-m-d', strtotime($_POST['departing']))

Ok, for the searching, I don't really have an answer but I thought I would participate since I will likely be asking the same questions anyway.

It is my assumptions that if we are using MySql DATE column types it will be easier as BETWEEN
should work properly without too many manipulations. If I am right, then:

$query="SELECT id FROM bookings WHERE $start_date !BETWEEN bookings.begin_date AND bookings.end_date"; 

should work (or a SELECT COUNT might be even quicker). That would tell us if the begin date was available (if no rows are returned the date is available).

I stumble with the next part, making sure the date is available through the entire range. I guess the long way would be to do the same for each day in the period. Any other ideas?

derksj: please update this thread if you come up with good answers elsewhere!

[datona]

BUMP!

I am also looking for a solution for searching multiple dates and telling me if all days between the start_date and end_date are available (not in the dbase)

Thanks to any and/or all that help us people out!