Hi everybody. right now i'm doing a hotel reservation system using php and phpmyadmin. The process involved
1) user inputs check-in and check-out dates (to check what rooms are available during the dates input)
2) database is queried for all room categories AVAILABLE within the dates indicated
step 1 and 2 work out well using the query below :
SELECT rt.roomtypeID, rt.roomtype, rt.roomprice
FROM roomtype rt
INNER JOIN room r ON rt.roomtypeID = r.r_roomtypeID
WHERE r.room_status = 'available'
AND r.room_no NOT
WHERE checkin >= '2010-04-04'
AND checkout <= '2010-04-06'
GROUP BY rt.roomtypeID
Then a problem arise..
How can I assign ROOM NO for any customer who has just make a reservation.
I have a 'room table' and 'roomtype table'.
right now when user make a reservation, i will assign them a random number based on the roomtype they had choose.
example : roomtype Single=10 rooms, Deluxe=10 rooms, Suite=10 rooms.
right now this is the only things that i can think right now..
(SELECT room_no FROM room WHERE r_roomtypeID ='single' AND room_status='available' ORDER BY RAND( ) LIMIT 1");
And it did work out. But then, i was thinking, how can i automatically assign the status of"unavailable" for the room no that was just assign to the customer who had just make reservation.
So, next time if another customer wanted to make a reservation, the random number that will be selected will not involved the room that has status room_status='unavailable'.
I appreciate any ideas, keyword too google for , or any articles that i can refer to in solving this matter:)