Rent A Car - How to check if car is available between two dates?

Question

I'm making a rent a car site and cars can be reserved for a given time period. For example a car is reserved between 01.01.2011 - 10.01.2011 and 15.01.2011 - 25.01.2011. (dd.mm.yyyy)

So the car is available between 11.01.2011 - 14.01.2011.

How should I keep the records of reservations? If I make a table named 'reservations' and create columns for 'carID' 'startDate' 'endDate' how can I check if the car is completely available between two dates? Or how should I create the reservations table?

Answer 1

I'd do it using timestamps. I know that MySQL has built in DATETIME field types and what not but I generally prefer working with timestamps. Anyway here's how I would go about it

vehicles table:

id | name 
---------
1  | Ford Mustang

reservations table:

id | vehicle_id | start_time | end_time
----------------------------------------
1  | 1          | 130500040  | 130599304

Where start_time and end_time are UNIX timestamps.

Then when you want to see if a car (change the vehicle_id in the query depending on which car) is available on some date:

$start_date = strtotime($start_date);
$end_date = strtotime($end_date);
$query =  mysql_query("SELECT * FROM `reservations` WHERE `vehicle_id`=1 AND (`start_date`>".$start_date." AND `end_date`<".$end_date.") OR (`start_date`<".$start_date." AND `end_date`>".$end_date.") OR (`start_date<".$end_date." AND `end_date`>".$end_date.") OR (`start_date`<".$start_date." AND `end_date`>".$start_date.")");

if (mysql_num_rows()<1)
{
   echo "The vehicle is available";
}