asked on

how do I write a mysql query to check if is an item is available during the time period requeste

Hey there experts :) ,

I am building a system for booking a room, and I now need to work out how to tell if the room is already booked during the a requested time period

In a booking table on my mysql database I have two datetime fields called "start" and "end" which contain the begining and ending times of each booking.

How do I write a query that compares a requested "start" and "end" with those already in the database and determins if the time period is available or not?

e.g.

If a user reqest a booking to start: 2010/05/12 08:00 and end: 2010/05/12 10:00

I need to be able to compare this to my bookings table and check if the period between 8 and 10 oclock on the 12th april 2010 is already taken

theremon

Hi there

I think that somethink in the lines of the following query could help. Dates used are your examples.


SELECT * FROM `bookings` where roomid=1 and 
   (
    (('2010-05-12 08:00' between startdate and enddate) or 
    ('2010-05-12 10:00' between startdate and enddate))
   or (startdate between '2010-05-12 08:00' and '2010-05-12 10:00')
   or (enddate between '2010-05-12 08:00' and '2010-05-12 10:00')
   )

Open in new window

ASKER CERTIFIED SOLUTION

flytox06

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theremon

flytox,

don't want to be rude and seem like I pass judgement on your answer, but it's wrong. The query you posted will cover only one of the possible date combinations. It'll return >0 only if there's an existing booking that starts before the given start date and ends after the given ending date. There are several other cases however. For example, say there's a booking that starts before the given date but also ends before the ending date. Or it starts and ends between the given dates. And so on, so forth.
Your query doesn't cover all that.

flytox06

No worries for the judgement, a feedback is always welcome positively. But it does cover all cases, please re-read the corresponding dates in filter.

Note: advantage of this method is that you make a better use of indexes on date.

theremon

Ok

I just fell flat on my face :)
This is totally not my day. I completely missed how you had reverted the dates.
My apologies.

flytox06

no prob, it's not easy to get at first shot in fact since it's a "NOT" way of thinking :-)

theremon

Nah, reading the query makes perfect sense. I guess I'm too tired today and just read the dates as I had them already in my mind - ie. I didn't actually READ what you had typed...

Susurrus

ASKER

wow, I didn't realise my little question would illicit such attention and discussion :). Thanks alot guys, I will try it out shortly and tell you how I got on and of course alot those ever important points ;)

gjanssenmn

This works for a static time and static date, how about for a static time but a dynamic date using Date() function? Say I always want the items between 10:00 am and 11:00 am 'yesterday'?