Forum Moderators: coopster
heres the deal, i have a user input form, which takes the "arrival date" from a user.
the takes "no. of nights" from user.
from that, i can work out the "departure date", and have done.
the date in the MySQL database (Im using MySQL Administrator) column type is just DATE.
Ok, f'rinstance, John appears on the 1st stays for 3 nights so departs on 4th in room 1.
Sally appears on the 2nd and stay for 1 night leaving on the 4th
Ive created a html table that displays:
1st ¦ 2nd ¦ 3rd ¦ 4th
on it. That was made using a PHP function, so that the 1st day is the current day, and the next onw is tomorrow nd so on for like 7 days.
now, in this same html table, each row, is a room number, these numbers are also taken from a database. sp i now have...
1st ¦ 2nd ¦ 3rd ¦ 4th ¦ 5th
Rm 1
Rm 2
Rm 3
Ok, in each column, id like to display the number of FREE spaces in each room, assuming room 1 has 4 beds,
1st ¦ 2nd ¦ 3rd ¦ 4th ¦ 5th
Rm 1 3 3 2 3 4
Rm 2
Rm 3
Ive tried mulling the problem over in a fair few different ways, but it doesnt seem to want to do anything.
Any input, logic or coded would be appreciated
Pseudocodeing...
(this code will be replicated in each cell)
SELECT guest_number, departure date, arrival date, room number FROM guest table WHERE room number = (the room number on the left)
for each guest{
if (departure date - arrival date >=1)
{current occupants +1}
}
Echo ($current occupants)
Ok, i can see a couple of funky points already.. as in, the date formula, wont return a boolean value... maybe gotta work out some way of making that so...
Any thoughts?
the only reason I say that is it might make the availability of each room spot easier to calculate.
if all you have is a range then you need to calculate each room and do math on every range to show that table.
you could then query by room or by guest to get the data you would need for any given part. Old data could be archived to keep db size down.
just thinking out loud along with you
The first thing I'd suggest is to build the answers in arrays instead of worrying about structuring it so it outputs an html table top down - that relieves some pressure structure wise. Then you can think about the problem in terms of a day at a time and have a couple of simple loops to spit the array contents out in html table form at the end.
You might also want to have two tables: a guest table with arrival, departure etc. and another 'bookings' table that's room oriented. Otherwise it could get a bit tricky keeping guests from bouncing around amongst the rooms (they'd all get huddled up in the lowest numbered rooms), and it may make the code a bit easier if/when a guest changes any dates after being booked. The bookings table would have fields for room number, guest number, and occupancy date.
A new guest entry, then, would involve adding a single row to the guest table and adding an entry per night into the bookings table - start with room 1 and check its availability for each of the necessary nights, moving up amongst the rooms until one is available for all.
Retrieving room status would entail a query that's grouped by room - if you prefer simpler queries (I know I do <grin>) you could do separate queries for each room and each day using mysql's COUNT function to tell you what you want to know.
Does that help?
