Query Trouble with booking system

Stinky · 22 Mar 2006 at 12:53

I want the code to Search the database for rooms already booked on the date required.

for example. I have 5 Double Standard Rooms available, when the customer pushes submit it needs to search for booking between the dates that they wish to book. If all 5 rooms are booked then return a page saying so, if rooms are avalable then continue to book the room.

this is the code i have atm

Code:

$query = "select roomNo, price from Room where type= '$roomType'";
            $result = mysql_query($query);
            $getTotalRecords =  mysql_num_rows($result);
            $i = 0;
            while($row = mysql_fetch_array($result)){
            	$i++;
            	$query1 = "select * from Reservations where roomNo = '{$row[0]}'";
                $result1 = mysql_query($query1);
                $row1 = mysql_fetch_array($result1);
                if($row1) {
                    if($startDate >= $row1['dateIn'] && $startDate <= $row1['dateOut'] ||
                       $finishDate >= $row1['dateIn'] && $finishDate <= $row1['dateOut']) {
                       if($i == $getTotalRecords){
                        	echo "All rooms are booked at the moment. Please go back and choose another period.";

Stinky · 22 Mar 2006 at 19:36

anyone got any idea??

Garrett · 22 Mar 2006 at 21:08

What happens when that code runs? i.e. whats wrong with it?

Stinky · 22 Mar 2006 at 21:52

atm it doesnt come back with any results.

basically you can book the same room as many times as you want. but only 5 Double Standard rooms are available

Haircut · 22 Mar 2006 at 22:43

Why are you messing about with all that PHP?
Having a quick look at your data model you should be able to do it in the SQL.

something like

Code:

"SELECT rm.roomNo
 FROM room AS rm
 WHERE rm.type = '$roomType'
 AND NOT EXISTS
    (SELECT 1
     FROM reservations  rs
     WHERE rs.roomNo   = rm.roomNo
     AND   ((rs.dateOut BETWEEN '$startDate' AND '$finishDate'
             OR rs.dateIn BETWEEN '$startDate' AND '$finishDate'
            )
            OR rs.dateIn <= '$startDate' AND rs.dateOut >= '$finishDate'
           )
    )
"

This query returns all the rooms that are available on the chosen dates, thus if no rows are returned you can see that all rooms of that type are booked.