[EMAIL PROTECTED] wrote:
<snip> |--------------------------|--------------------------|--------------------- -----| | MON JUL 5 | TUE JUL 6 | etc... | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| Rm # |Rm Type | First Name | Last Name | First Name | Last Name | etc... |Rm Type |Rm # | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| 101 | NQQ | Isaac Newton | (empty room) | etc... | NQQ |101 | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| 102 | NK | Alexander Flemming | Alexander Flemming | etc... | NK |102 | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| 103 | K | Charles Darwin | Robert Kennedy | etc... | K |103 | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| 104 | QQ | Roger Rabbit | (empty room) | etc... | QQ |104 | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----| ... | ... | ... etc | ... etc | etc... | ... |... | -----|--------|--------------------------|--------------------------|------- -------------------|--------|-----|
<snip>
Hi
I know this not what you asked and please feel free to tell me to mind my own business, but a more useful design for your DB would be something like this:
Table : rooms Fields: room_number (PK), room_type
Table: Customers Fields: customer_id (PK, autoincrement), first_name, last_name, etc, etc
Table: bookings Fields: room_number, booking_date, customer_id - PK(room_number, date)
In the short term, changing your db will be a bit of a pain cos of the code rework, but the db design will be more scalable and more useful in the long run. To get the display that you required you can do something like this - assuming that you want a seven day span starting from today (for example) - showing only the first two days
$query = SELECT a.room_number, a.room_type, IFNULL(CONCAT_WS(" ", b.first_name, b.last_name), "Empty Room"), d.room_number, d.room_type, IFNULL(CONCAT_WS(" ", e.first_name, e.last_name), "Empty Room")
FROM rooms a LEFT JOIN bookings c ON a.room_number = c.room_number AND c.booking_date = "2004-07-09", customers b, rooms d LEFT JOIN bookings f ON d.room_number = f.room_number AND f.booking_date = "2004-07-10", customers e
WHERE b.customer_id = c.customer_id AND e.customer_id = f.customer_id
You will still have to add more code for the other five days, but if you want to be really clever and save wear and tear on your keyboard you can build the query dynamically using a loop...
You don't have to use a query this complicated, but if you do, you shift the bulk of the data processing and arranging to the DB (which does it a lot more efficiently than PHP). Then you just have to use PHP to create the table, the relevant headings and to populate the cells.
Ok, I am finished sticking my oar in...
Rory
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
