Help with Query to group rooms free between particular dates under their respective hotels

Question

Hi Everyone... I am building an online hotel booking system.... Using php and mysql.... Users can search for and book hotels in a particular location .. The search criteria will be
1. City or country
2. Check - in and Check Out dates
3. Number of guests
My sql schema is as follows....

1. Table : hotels
   hotel_id
   hotel_name
   country_id
   city_id
   
   

2. Table : rooms
   room_id
   hotel_id
   room_name
   room_price
   max_guests_allowed
   no_total_rooms
   no_booked_rooms
   
   

3. Table : reservations
   reservation_id
   room_id
   hotel_id
   check_in_date
   check_out_date
   
   

Query to get rooms free between two given dates and can accommodate persons equal to or more than the no. of persons given by the user :

SELECT rooms.room_id, 
       rooms.room_name, 
       rooms.max_guests_allowed, 
       rooms.room_price, 
       hotels.hotel_id, 
       hotels.hotel_name 
FROM   hotel,rooms 
WHERE rooms.id NOT IN (SELECT room_id 
                       FROM   reservations 
                       WHERE  check_in_date < '$arrivalDate' 
                       AND    check_out_date > '$leaveDate') 
AND   rooms.max_guests_allowed >= '$no_of_guests'; 

Now... I have to display the result like....

1. Hotel 1 
     Room 1     No. of Rooms Free     Price   Max. Guests Allowed 
     Room 2     No. of Rooms Free     Price   Max. Guests Allowed 
     Room 3     No. of Rooms Free     Price   Max. Guests Allowed

2. Hotel 2
     Room 1     No. of Rooms Free     Price   Max. Guests Allowed 
     Room 2     No. of Rooms Free     Price   Max. Guests Allowed 
     Room 3     No. of Rooms Free     Price   Max. Guests Allowed

etc.. etc...

How should i modify my query to group the rooms under the hotels like i have mentioned above...???? Thanks a lot in advance....

Answer 1

I may be misunderstanding the question/query slightly, but couldn't you add:

ORDER BY hotels.hotel_name ASC,rooms.room_name ASC

to the end of the query to give you the results in hotel and then room order?

Edit to display in your original format, you could use some code similar to this. I've done it in PHP here as you haven't specified what language you're using:

$currentHotel = -1;
while ($row = mysql_fetch_array())
{
  if ($row["hotel_id"] != $currentHotel)
  {
    print "Hotel " . $row["hotel_id"] . "\n";
    $currentHotel = $row["hotel_id"];
  }

  // print your room details out here
  print "Room " . $row["room_name"];

  // ...
}

Answer 2

http://www.bestchinahotel.com/