[SOLVED] MySQL - Date Range for reservations

[web506]

Hi everyone

 

I found this website after looking and looking for an answer for my project, is 5.36 am and my eyes are tired.

 

I bought PHP book, for dreamweaver that had a tutorial - hotel reservation. This is not a Dreamweaver issue, it is just that the book project was not finished as I expected. I think there is a lot more you could do and learn from this tutorial on.

 

It just let people do a reservation, but it does not have like a calendar where you can actually see if rooms are booked or not in a period of time like from 2008-01-01 to 2008-01-05 show as not available but before or after this period show the room is available.

 

if I have 4 rooms, do I need to have like a separated schedule for each room?

 

How can a row show BOOKED from  2008-01-01 to 2008-01-05 and available before/after that time?

 

Thanks a lot!

 

 

 

 

[Barand]

I'd store the bookings for each room as

 

bookingID | roomNo | arriveDate | departDate | clientID

 

Then it's easy to produce

[pre]

Jan  | 1| 2| 3| 4| 5| 6| 7| 8| 9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|

      +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Rm 1    X  X  X  X  X

      +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Rm 2                        X  X  X

      +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Rm 3                X  X  X  X  X

      +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Rm 4            X  X  X  X  X

      +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

[/pre]

[Barand]

In the above example, if you get a new booking arriving on 2008-01-05 and departing 2008-01-08, the following query gives room 2 as vacant

 

SELECT room FROM room r
LEFT JOIN room_booking b
ON r.room = b.roomID AND b.arriveDate < '2008-01-08' AND b.departDate > '2007-01-05'
WHERE b.roomID IS NULL

 

--> 2

[web506]

thats great,

 

thanks a lot for your help, let me test it, I will let you know for sure,

 

 

[web506]

I tried that, but it did not work. 

 

Do I have to create a booking table for each room? cause I have 3 tables.

 

Clients - Bookings - Rooms

 

BOOKINGS

-------

ID   

roomID   

clientID   

startDate   

endDate   

adults   

children   

roomType   

roomOptions   

networkConnection   

PC   

requirements

 

-----------------------

 

ROOMS

-------

id   

price   

bed   

a/c   

description   

number

 

And this is the recordset I'm using to search for available rooms.

 

SELECT rooms.number

FROM rooms LEFT JOIN reservas ON rooms.id = reservas.roomID AND reservas.startDate < '2008-01-01' AND reservas.endDate > '2008-01-05'

WHERE reservas.roomID IS NULL

 

and I have a reservation on room 7 and 8

for those dates, but they do show in the recordset result.

 

is it maybe the reservation process?

 

no, because those reservations have a roomdID in there.

 

please help me, or I will loose all my hair

 

 

[web506]

Actually, I got it!

 

My bad, I just changed arrival date and departure date, I had them in the wrong place, thanks a lot. This is greaT!!!