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.