How do I use price data in one table for a calculation that is stored in another table?
- by shane
I'm still leanring PHP/MySQL but have learned quite a bit thanks to codies on StackOverflow. I'm trying to setup a sort of room reservations system using two tables:
SETUP:
Room price table: Has, prices for a type room a client may want to rent as well as the dates (day of week) they wish to use it. Pricing varies based on day of the week and per room.
I've setup a different table for each room type as each room type carries different pricing for each day of the week. So, There is an Alpha room table, Bravo room, etc. Within Alpha table are headers for the days of the week with pricing pre-entered into the rows.
Client info table: Has the name, address, date of room use, etc data for the specific client.
EXAMPLE:
Alpha-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.
Bravo-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.
Client data table:
ClientName; date-of-room-use; address; day_subtotal; grand_total.
QUESTION:
I'm trying to find PHP code that will:
look at the date of room use in the client data table,
look up the associated cost for that date in the specific room pricing table,
record that unit cost in the day subtotal of the client data table
and sum a grand total in the grand total row of the client data
table (assuming the room may be used more than one day by the
customer).
I know there's something to do with join but I'm finding it difficult to grasp the concept and, if someone can demonstrate using this example, I think I will have a better understanding of how to work this sort of transaction.
Thank you ALL in advance for your suggestions or alternatvie approaches.
