Page 1 of 1

Checking dates in a database

Posted: Fri Apr 09, 2004 3:03 pm
by gerrymac
Hi

I am developing a system which will allow user to book online hoilday homes. The user will specify the date they wish to rent from (startdate) and the date that they wish to leave (endDate) My problem is the fact of double booking. In theory a user could book a week to stay in three months time. How would i ensure that no other booking is made for that week, also if a booking for the house has been just made two days before the first startdate but runs into the week that has already been booked, the system should also not allow this as it would lead to a double booking for some of the week? Could anyone tell me how i would query the database in order to achieve this?

Thx
Gerry

hmmmmm

Posted: Fri Apr 09, 2004 11:29 pm
by dipit
hello Gerry,

can u send us the whole picture to tables, which are carrying the info to booking.


dipit

Posted: Sat Apr 10, 2004 8:49 am
by gerrymac
Hi

I will be having a system that enables users to book online holiday homes. My tables are customer, custorder, house and also caretaker but this last table does not affect this problem(i think!). Users will be able to view the hous they wish to rent and they do this via a booking form. They select a house from a drop down list which is extracted from the house table. They then enter their details. Their name and address etc is inserted into the customer table and an entry is made into the custorder using insert_id() fuction. In the custorder table i am inserting data such as the house booked credit card no, type AND the dates in which they want to book the house for. The first booking will be no problem as they can select any date but when a new booking is about to be made i need a check on the existing bookings in the datbase to ensure that the new order is not for the same house and the same period of time. Below is how my tables bascially look.

Customer
custid
name
address1
address2
phoneno

Custorder
custid
booking start
booking end
house name
credit card details

Posted: Sat Apr 10, 2004 9:22 am
by kettle_drum
Well im sure that all the bookings will be for whole days, i.e. you cant book for 7.5 days. So just grab the dates of all the bookings from the database, then make an array of the days which this bookings are for, then check these days against the new booking days.

Posted: Sat Apr 10, 2004 9:40 am
by Weirdan

Code: Select all

select count(*) from custorder where `house name` = '$house' and ($startdate between `booking start` and `booking end` or $enddate between `booking start` and `booking end`)
would give you non-zero result if that house already booked for the given [possibly partial] period and zero otherwise.

Posted: Sun Apr 11, 2004 9:12 am
by gerrymac
hi thank guys for suggestions

kettle_drum, i have no problem querying the database say like:

select * from custorder where house = $house and startdate = $startdate or enddate = $enddate. however as far as I can tell me this will only prevent booking, on them actual days. New customers could still potentially book for a period starting during a period that was already booked for.


i think the other suggestion might work but i have not come across any "between" function in PHP yet. If there is one this would probably solve my problem