Double bookings problem mysql

Databases
1

Hi guys,

hope im in the right forum for this. Im trying to build a property rentals database with a mysql db. I have an availability table with start date and end date, booking id, property id etc. After a booking is made the user needs to be able to adjust his booked dates, maybe extend for a day or two, whatever.

So i need to check the Av table just for that property, but excluding its current bookingID so that it remains available for itself to be booked. I need to see all the start and end dates that it has, compared to the users start and end dates. If the users start and end dates clash with any other dates booked for that property I will return a count of more than 0 which means the property cant be booked.

Im really struggling on the sql for this and just cant get it right - please help.

heres my statement

strSQLAP = " SELECT * FROM tblavailability WHERE bookingID <> " & intBookingID &" AND propID =“&intPropID&” AND ((‘“&strStartDate&”’ BETWEEN startDate AND DATE_SUB(endDate, INTERVAL 1 DAY) OR ‘“&strEndDate&”’ BETWEEN DATE_ADD(startDate, INTERVAL 1 DAY) AND DATE_ADD(endDate, INTERVAL 1 DAY)) OR (startDate BETWEEN DATE_SUB(‘“&strStartDate&”’, INTERVAL 1 DAY) AND DATE_ADD(‘“&strEndDate&”’, INTERVAL 1 DAY) )OR (endDate BETWEEN DATE_SUB(‘“&strStartDate&”’, INTERVAL 1 DAY) AND DATE_ADD(‘“&strEndDate&”’, INTERVAL 1 DAY) ) );"

If strStartDate matches a db end date its returning a record and i dont want it to, time is always 12:00:00 so you can have a start and end in same day.

if strStartDate and strEndDate straddle a db start and end it is finding a record and showing i cant book, which is correct.

If strEndDate is 1 day less than a db start date it shows a record so it cant be booked - thats wrong, strEndDate can the same as dbStartdate.

if strStartDate and strEndDate are both within a db start and db end date, its throwing a record out which is correct and i cant book.

i think they are the four conditions i need to look for.

Thanks in advance for any help, im really confused with this.

2

From what I understand the db is returning a record you don’t want because the start day and end day may fall on the same date, but you can still have a property available at a certain time of the day?
If so try using a TIME or TIMESTAMP columns in the table and compare the time with the dates.
Hope this helps if I understand the situation correctly.

3

hi, thanks so much. im using DATETIME to store the date and time at the mo. Are you saying to split time and date into DATE type and TIME type. Er, but even if i do that i’ll still have to run over the same sql no ? to see whats booked, in fact wont i have to do that once for date and once for time ? Can you tell me how the TIME type will make such a difference - im a bit of a newbie on mysql dbs ! thansk so much again.

4

Read this article
http://www.tizag.com/mysqlTutorial/mysql-time.php
this should help you

5

or try TIMESTAMP column and compare values

6

If I understand correctly, a person may leave on a Saturday and another come in on that same Saturday. I would use a TIME column.
If you know what time a person is leaving and another would be coming in, I would use the time. You are saying that the DATE column is giving you 12:00:00? I would use DATE as my column type for the date and use TIME column for the time they would come or go.
I you want to make sure no double bookings, I would narrow it down to time and date rather than a date. Then compare the Time and Date fields for results. Hope I helped out a little

7

hi, thanks so much for replying. I’ve just looked through these, the last is the closest, but still seems a lot less complicated than what im trying to do, or maybe its me being so confused on this. Ive scoured the net looking for example code or othjer peopes problems and there seems to very little about. Can this be done in 1 sql statement or am i approaching this problem from totally the wrong way ?. Ive tried sub select as well, but still get the same result. Clearly my sql knowledge isnt the best, maybe ive wriiten something wrong way.

Anyway thanks again, if you or anyone can shed any light on this id be really grateful !

8

Yes, strStartDate can be same as dbEndDate because all dates are always set at 12:00:00, which is what you want as in reality a holidaymaker can leave a property in the morning and another come into it in the afternoon. Likewise strEndDate can be same as dbStartDate.

What im trying to do is check to see if strStartDate AND strEndDate are between a dbStartDate and dbEndDate, OR
if strStartDate is between a dbStartDate and dbEndDate or
if strEndDate is between a dbStartDate and dbEndDate or
if a dbStartDate or dbEndDate are between strStartDate and strEndDate.

If a record is returned it means an overlap a wrap or an inner booking has been found, therefore strStartDate and strEndDate cant be booked as the record count would be more than 0. If it is 0 as no records have been returned then the property is free and can be booked. with me ?

Not sure how TIME can help in this ? I dont know if im going about this the wrong way or if my sql is just short of being right !!! help :slight_smile:

9

does any of these threads help?

http://www.sitepoint.com/forums/showthread.php?t=607741