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
Checking dates in a database
Moderator: General Moderators
hmmmmm
hello Gerry,
can u send us the whole picture to tables, which are carrying the info to booking.
dipit
can u send us the whole picture to tables, which are carrying the info to booking.
dipit
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
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
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`)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
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