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
SusurrusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theremonCommented:
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

0
flytox06Commented:
SELECT COUNT(*)
FROM booking
WHERE start <= '2010-05-12 10:00'
AND end >=  '2010-05-12 08:00'

If count = 0, then your room is free.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theremonCommented:
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.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

flytox06Commented:
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.
0
theremonCommented:
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.
0
flytox06Commented:
no prob, it's not easy to get at first shot in fact since it's a "NOT" way of thinking :-)
0
theremonCommented:
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...
0
SusurrusAuthor Commented:
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 ;)
0
gjanssenmnCommented:
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'?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.