CODEWITHSUNDEEP
phphtmlmysqldate
SmT
SmT

Reputation: 335

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

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?

Upvotes: 2

Views: 5590

Answers (3)

Dormouse
Dormouse

Reputation: 5198

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";
}

Upvotes: 6

Cups
Cups

Reputation: 6896

Here's another way I found (via Mysql filling in missing dates ). Create 3 tables: vehicles, firm bookings only and all available dates for the year.

reservations:  id, booked, vehicle_ref

insert into reservations values (0, '2011-01-12',1);
insert into reservations values (0, '2011-01-13',1);
insert into reservations values (0, '2011-01-14',1);
insert into reservations values (0, '2011-01-20',1);
insert into reservations values (0, '2011-01-21',1);

reservations_free: free (just a list of all dates this year...)

insert into reservations_free values
('2011-01-10'),
('2011-01-11'),
('2011-01-12'),
('2011-01-13'),
('2011-01-14'),
('2011-01-15'),
('2011-01-16'),
('2011-01-17'),
('2011-01-18'),
('2011-01-19'),
('2011-01-20'),
('2011-01-21'),
('2011-01-22'),
('2011-01-23');

Retrieve all dates which are not booked for vehicle #1 between 1 jan and 1 feb:

SELECT free
FROM reservations_free
LEFT OUTER JOIN reservations ON (reservations.booked = reservations_free.free AND reservations.vehicle_ref =1)
WHERE booked IS NULL AND
reservations_free.free
BETWEEN '2011-01-01'
AND '2011-02-01'
ORDER BY free
LIMIT 0 , 30;

Gets you the list:

2011-01-10
2011-01-11
2011-01-15
2011-01-16
2011-01-17
2011-01-18
2011-01-19
2011-01-22
2011-01-23

Of course you'd have to maintain the reservations_free table every year, that sql statement might need further optimising as I only fiddled with it from the original.

Using mysql DATE fields means you can browse your data and it will still make some sense to you as a human.

Adding and deleting a day would be a cinch.

It does not deliver the "free between 15th and 19th Jan" as asked for, but that should be doable in PHP, if not in a slightly more complex sql statement.

Upvotes: 1

RRStoyanov
RRStoyanov

Reputation: 1172

Create two tables - one to hold your vehicles information and one which holds all your bookings (as you describe in your question). When you need to check availability, only query the bookings table.

Be aware of situation where your client may want to overbook or not return the car on time. Or the car is returned in very bad condition and you need to service it first. There a lot more unknown situations, so IMO you should allow at least +/- 1 or 2 days of gap between bookings. If you have more than one car of a type, than you may face not so many problems.

Upvotes: 2

Related Questions