Checking dates in a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Checking dates in a database

Post 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
dipit
Forum Newbie
Posts: 12
Joined: Thu Apr 08, 2004 2:36 am
Location: india
Contact:

hmmmmm

Post by dipit »

hello Gerry,

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


dipit
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Post 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
Post Reply