Posted by Steve on 10/26/06 13:47

"Pankaj" <panahuja@gmail.com> wrote in message
news:1161867323.670470.187190@h48g2000cwc.googlegroups.com...
| between would fetch me all records in the given range. It would not
| distinguish if the value of the field is 0 or 1 (available or booked)
|
| > I don't s'pose this is really a php issue, it'll depend on what database
| > you're using. Presumably whatever that is it'll give you a way to
effect
| > "between", because you want to find any existing bookings *between* the
| > start and the end date that your prospect is trying to book.
| >
| > It may be doable in a single query. It might depend on your table
| > structure of course.
| >
| > Something like :- (serving suggestion)
| >
| > SELECT * FROM Bookings WHERE BETWEEN(BookingDate, NewStart, NewFinish)
| > AND Room=theRoom;

uhhhmmm...if BOOKINGS were a table that stored only room id's and the date
when it was NOT available, then that query would work FINE...if you changed
it to SELECT COUNT(*) and then wanted the value 0 to be returned to show a
room as available.

as it is, you've given NOTHING helpful in the way of details that helps us
help you.

btw, if you are wanting a query to only show what rooms were available for
selection, this would suffice:

SELECT r.Room ,
r.Description
FROM rooms r ,
(
SELECT Room
COUNT(*) Availability
FROM bookings
WHERE Reserved >= ' . $startDate . '
AND Reserved <= ' . $endDate . '
GROUP BY Room
) b
WHERE b.Room = r.Room
AND b.Availability = 0

not a big or complex query at all.

[Back to original message]