I need help, CFquery and SQL NOT BETWEEN

Report · May 23, 2010

OK I am building a hotel booking system as a college project and I'm having trouble with finding rooms that are available. Now I have got this working, to an extent, So to give you the bigger picture al try and lay this out the best I can so you can understand.

This is my database structure:

Bookings Table

Rooms Table

Customers Table

BookingID

BookingDateIN

BookingDateOUT

RoomID

CustomerID

RoomID

RoomType

RoomNumber

RoomPrice

Room Description

CustomerID

FullName

AddressLine1

AddressLine2

PostalCode

CarReg

OK, now you can see how this is going to work with the Bookings Tables RoomID and CustomerID matching the others to make a relationship, simple databasing.

Now I have a page that lets you select a date and then select how many days the customer wants to stay for. So the selected date will be put towards the BookingDateIN field and the calculated date will be put towards the BookingDateOUT in a NOT BETWEEN SQL statement.

I have got this to work with some test data, how ever when I select a date range that is below the 5th day in any month it shows the rooms are booked and not available even though the test data does not contain any days below the 5th.

This is my test data in the Bookings Table:

BookingID	BookingDateIN	BookingDateOUT	CustomerID	RoomID
1	20/05/2010	23/05/2010	2	2
2	26/05/2010	28/05/2010	1	5

And here is my code for the bookings page:

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif isDefined("FORM.SearchRooms")>

<!-- adds the number of days to the selected date -->
<cfset #CalDate# = #DateFormat( DateAdd( 'd', #FORM.SelDays#, #FORM.SelDate# ), 'dd/mm/yyyy' )#>

<!-- Queries the Bookings table with a crazy NOT BETWEEN SQL Statement -->
<cfquery name="Check_Rooms_RS" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
    SELECT *
    FROM Bookings
    WHERE BookingDateIN AND 
    BookingDateOUT NOT BETWEEN 
    <cfqueryparam value="#FORM.SelDate#" cfsqltype="cf_sql_timestamp"> 
    AND 
    <cfqueryparam value="#CalDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!-- Now this is my beginner CFQuery'n lol it queries the Rooms table with the values output from the bookings query and loops, then I put a 0 at the end to have the correct SQL statement lol I'm trying -->
<cfquery name="Rooms_RS" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
SELECT *
FROM Rooms
WHERE RoomID = <cfloop query="Check_Rooms_RS">#RoomID# OR </cfloop>0
</cfquery>

</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Hotel Booking System - User Manager</title>
<link rel="stylesheet" type="text/css" href="../style.css"/>
</head>

<body>
<div class="container">
    <div class="booking_choose_date">
         <cfparam name="PreSelDate" default="#LSDateFormat(Now(), 'dd/mm/yyyy')#">
<cfif isDefined("FORM.SearchRooms")>
    <cfset #PreSelDate# = #FORM.SelDate#>
</cfif>
        <cfform name="form1" width="375" height="350" action="#currentpage#">
<!-- Date Picker -->
           <cfinput type="DateField" name="SelDate" label="Block out starts" width="100" value="#PreSelDate#" mask="DD/MM/YYYY">
<!-- Day Picker -->
<cfselect size="1" name="SelDays" required="Yes" message="Select days staying">
      <option value="1" selected>1 Day</option>
      <option value="2">2 Days</option>
      <option value="3">3 Days</option>
      <option value="4">4 Days</option>
      <option value="5">5 Days</option>
      <option value="6">6 Days</option>
      <option value="7">7 Days</option>
</cfselect>

           <cfinput type="Submit" name="SearchRooms" value="Check" width="100">
        </cfform>
    </div>
    
     <cfif isDefined("FORM.SearchRooms")>
        <div class="search_results">

          <!-- Outputs the Bookings table RoomID values -->
            <cfoutput query="Check_Rooms_RS">
                 #Check_Rooms_RS.RoomID#
            </cfoutput>

          <!-- Outputs the Rooms table RoomID values -->
            <cfoutput query="Rooms_RS">
                 #Rooms_RS.RoomType#
            </cfoutput>   
            
        </div>
    </cfif>
</div>
</body>
</html>

Little help with this would be amazing !!

Report · May 23, 2010

what database are you using? please check your database's manual for the

correct syntax for BETWEEN X AND Y.

Report · May 23, 2010

First, I know it's a college project. But, you need to read more documentations about CF. Something's not right with the way you set variables (<cfset #CalDate# ...>). Unless you are trying to set a dynamic name for a variable, but even it's not the best way. And I don't recommend to use dynamic names for variables. It's a headache to debug.

Also, please examine your query. You're 2nd query (WHERE RoomID = <cfloop query="Check_Rooms_RS">#RoomID# OR </cfloop>0) is wrong. Is it not supposed to be IN? Even so, I think you can combine your 2 queries into one (make use of JOINs). Again, you can read first for more documentations to whatever DB you're using. It will help a lot.

Report · May 28, 2010

I only skimmed this thread. But even ignoring the myriad of date issues, the queries have a serious "logic" problem. If you think about it, does searching for rooms that are not reserved in a table that only contains reserved rooms really make sense? Say you have twenty (20) rooms. If only two (2) of them are reserved (ie in the Bookings table), how is that first query supposed to account for the other eighteen (18)?

It would make more sense to search the Bookings table for rooms that are reserved. Then query your Rooms table for rooms not in that list (ie available). Granted that is not a very efficient method for a real application. But should work for your purposes.

This is my test data in the Bookings Table:

Are you storing the booking dates as strings? Dates should be stored in a date/time column. Storing them as strings creates all kinds of problems. Not to mention it makes it more difficult to use your database's date functions.