sql statement ~ confusing :( dates

hedgehead

Hi, i'm just new to this, was wondering if someone could help me out possibly...

I have a booking system im working on where you can book a time period, from one date (checkindate) to another (checkout), these are stored in a mysql db.

I've written sql (u can see it below)

SELECT COUNT(*) AS bookings_count
FROM hotel.bookings
WHERE RoomID = '$rooms'
AND DateCheckin <= '$id1'
AND DateCheckout >= '$id2'

id1 and id2 being date variables passed on from another page...

the trouble is the count result is = 0 when a checkindate prior to an already created checkindate is made and and a checkout date is made at a date after the record in the db for checkout..

its kinda confusing, just wondering if any1 has an idea, or if the between statement can be used? only issue i was wondering about with the between is if it can be between and = to at the same time..
anyway thanks in advance, hope someone can shed some light

mrhappiness

what do you want to get?

hedgehead

well theres a start date and an end date input available for
users. sorry i wasnt too clear before, but what I mean is
that say a person checks in at a hotel on
6th June 2005
and checks out on
9th June 2005
and that is stored in the db (which i have currently working with the american date format)

with my entry form which writes to the db, it runs the code above counting if any records meet the criteria between dates and if count > 0 then the booking cannot be made

so it checks against the room numbers
and the user attempts to have a checkin date and checkout, but im not sure how to compare them

Using the dates above, the count would return zero if the person attempts to book from the 5th june 2005 to the 10th june... my logic is kinda fuzzy, i hope ive explained it better this time?

thanks in advance

mrhappiness

SELECT COUNT(room_id) free
FROM bookings
WHERE
  room_id = 5
  AND '2005-06-05' NOT BETWEEN datecheckin AND datecheckout
  AND '2005-06-10' NOT BETWEEN datecheckin AND datecheckout

what about that?

hedgehead

it works partly,
but if a booking is already sitting in the db
from the 6th june 2005 to the 7th june 2005,
it doesnt work if a person tries to book from say
the 5th june to the 8th june...

thanks for your help, i really appreciate it

mrhappiness

change the BETWEENS:

AND datecheckin NOT BETWEEN '2005-06-05' AND '2005-06-08'
AND datecheckout NOT BETWEEN '2005-06-05' AND '2005-06-08'