Select Available Rooms...

komlos

I'm doing a reservation or booking system and I stuck with this problem for a while. I have been searching the forum for days, but couldn't find the solution:

How can you select available rooms in a hotel for a given time period and list the hotel names (only the hotels that have free rooms for the given time period)?

I have the following database design:

booking

bookingid
roomid
startdate
enddate

rooms

roomid
hotelid
roomname

hotel

hotelid
hotelname

In table booking there are records only when a reservation is made.
startdate and enddate are DATE type fields.
User only enters $startdate and $enddate when search is made.

Thank you for your time!

Shrike

I think you'll need to get the whole lot of data, since sometimes there will be a record in booking, and sometimes not. So select from rooms, left join bookings on roomid and winnow the composite by the dates.

komlos

Thanks, but I don't really get it. Could you help me out with an example sql query?

Shrike

SELECT rooms.roomid, rooms.roomname
FROM rooms
LEFT JOIN booking ON rooms.roomid = booking.roomid
WHERE ( booking.startdate IS NULL AND booking.enddate IS NULL )
OR ( booking.startdate < $startdate AND booking.enddate < $enddate )
OR ( booking.startdate > $startdate AND booking.enddate > $enddate )

komlos

I have tried your solution but I had to modify it a bit. I think you meant something like this:

SELECT rooms.roomid, rooms.roomname
FROM rooms
LEFT JOIN booking ON rooms.roomid = booking.roomid
WHERE ( booking.startdate IS NULL AND booking.enddate IS NULL )
OR ( booking.startdate < $startdate AND booking.startdate < $enddate )
OR ( booking.enddate > $startdate AND booking.enddate > $enddate )

That means either both requested dates is before the startdate in db OR later than the enddate in db.

This works fine unless there are e.g. 2 records with the SAME roomid in the booking table, and one is overlapping with the requested dates (that is not available) and the other is not. In this case it will select the one which dates are not overlapping with the requested dates and list the room as available!

That's why I was thinking about an other logic, namely to select from bookings the rooms that are not available (dates are overlapping with requested dates), and then somehow exclude these from all the rooms in the table rooms.

What do you think? What sql query should I use in this case?

Thanks

Shrike

Hmm yes good point - does your database have subqueries? Should be possible to do it that way.

komlos

Unfortunately, not. Subqueries available only from mysql 4.1.
I have 4.0.22 Standard. I'm at a hosting company, on virtual account.

However, I found this:
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

komlos

Do you have any idea?

Shrike

The subquery I was thinking of would have been something like:

SELECT roomid FROM rooms
LEFT JOIN ( SELECT roomid AS bookedroomid FROM booking ... ) AS booking

But I'm not sure this is going to work either.

If your really stuck you could just select from rooms and left join bookings, without any WHERE condition and check the dates individually in PHP. Bear in mind that you are getting multiple rows containing the same roomid.

$qry = 'SELECT  rooms.roomid, rooms.roomname, booking.startdate, booking.enddate' .
$qry = ' FROM rooms' .
$qry = ' LEFT JOIN booking ON rooms.roomid = booking.roomid';
$result = mysql_query( $qry );
while( $row = mysql_fetch_assoc( $result ))
{
  if( $row['startdate'] >= $startdate && $row['enddate'] <= $enddate )
  {
    $bookedrooms[] = $row['roomid']; // Any id in this array is booked
  }
  if( in_array( $row['roomid'], $bookedrooms ))
  {
    continue;
  }
  else
  {
    $availablerooms[] = $row['roomid'];
  }
}