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?

check this: http://stackoverflow.com/questions/4165655/mysql-select-rows-where-date-not-between-date It's looks like a same question — Evgeniy Labunskiy, May 26 '11 at 12:41
You would have to query the cars table to see if carID is in the reservation table and then set your dates, hope this makes sense as it confused me writing it! — Liam, May 26 '11 at 12:44

Dormouse · Accepted Answer · 2011-05-26T13:39:58.420

6

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

edited May 26 '11 at 13:39

answered May 26 '11 at 13:00

Dormouse

5,130
1
26
42

As I see, he use format dd.mm.yyyy and not dd/mm/yy. So his format is easy convertible via the php core function strtotime. – RRStoyanov May 26 '11 at 13:06
but this way I check if the car is available for 1 date. I need to know if the car is available between 2 dates, for a period of time. – SmT May 26 '11 at 13:20
How do you generate pretty tables like that Ben? I always type out by hand and get the indentation totally wrong. – Layke May 26 '11 at 13:42
@Laykes, just typed them out by hand and then used the \`\` code markdown – Dormouse May 26 '11 at 13:50

score 2 · Answer 2 · answered May 26 '11 at 12:51

2

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.

answered May 26 '11 at 12:51

RRStoyanov

1,162
1
13
23

I don't think this has actually answered the question in anyway and I don't just say that because I also have an answer here. You haven't answered "how can I check if the car is completely available?" or really "how should I create the reservations table?" just that one is needed. The second paragraph is also less about programming and more about the car rental industry. -1 – Dormouse May 26 '11 at 13:13
Point taken for not providing the example (yours is good after the edit btw). On other hand, I don't think providing additional BUT helpful information should be avoided. – RRStoyanov May 26 '11 at 13:16

score 1 · Answer 3 · edited May 23 '17 at 12:18

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:

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.

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

3 Answers3