I'm building a hotel reservation system, for a relatively small number of hotels in a small town. Each hotel has a row in mysql table hotels, and each hotel room has a row in table hotel_rooms. Each hotel and each room has a list of amenities (pool, great view, etc.), some of which are linked to some standard amenities. This is done in order to let me search through the hotels and/or rooms to find one with a specific feature, while still letting the hotel owners have their way about what the text says ("luxurious pool" etc.). So I end up with tables hotel_amenities, hotel_room_amenities, hotel_terms, and hotel_room_terms (the last two storing the standard amenities).
Now, I'm trying to write a script to let users search for a hotel room based on the hotel's amenities, the room's amenities, and some other info (rate, etc). Being somewhat new at mysql and having been spoiled with subselects in mssql, I'm getting stuck on the query.
The logic is this: find hotel rooms with these features (refered to by the ID in the terms tables) in hotels with these other features, and also where rate < $x, etc. It's not that simple, because the rate fluctuates based on the date of the year and of the week, but the part I'm stuck on is querying based on the features.
Obviously I don't expect a working example, but if someone could point me in the right direction I would appreciate it. And as always, if anyone wants more information, just let me know.
Many thanks,
Keegan
Now, I'm trying to write a script to let users search for a hotel room based on the hotel's amenities, the room's amenities, and some other info (rate, etc). Being somewhat new at mysql and having been spoiled with subselects in mssql, I'm getting stuck on the query.
The logic is this: find hotel rooms with these features (refered to by the ID in the terms tables) in hotels with these other features, and also where rate < $x, etc. It's not that simple, because the rate fluctuates based on the date of the year and of the week, but the part I'm stuck on is querying based on the features.
Obviously I don't expect a working example, but if someone could point me in the right direction I would appreciate it. And as always, if anyone wants more information, just let me know.
Many thanks,
Keegan