Skip to main content
A
Anonymous
August 21, 2007
Question

Searching on date ranges

  • August 21, 2007
  • 2 replies
  • 354 views
I'm trying to create a booking system where the user enters an arrival date and a departure date. They then see a list of available room types based on the arrivalDate and the departDate. I'm having trouble on the Where clause.

I have used the following


<cfquery name="qGetRooms" datasource="booking" username="root" password="IreneB">
SELECT book_id, arrival_date, depart_date, booking_date
FROM booking
WHERE booking.arrival_date= '#form.arrivalDate#' and depart_date = '#form.departDate#'
</cfquery>

However, when I try to output this to a table, it's just blank
    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    August 21, 2007
    The database columns are date and times in MySQL 5. the form elements would be strings as they're simple text boxes. That the user types in dates to

    P
    paross1
    Participating Frequently
    August 21, 2007
    ....then I am curious how you expect to have a query return rows when you are essentially saying WHERE apples = oranges?

    Try something like converting your DATETIME columns using the DATE_FORMAT(date,format) function available in MySql, then do the same to the form variables using the ColdFusion DateFormat(date [, mask ]) function using the same format.

    Phil
    C
    cf_dev2
    Inspiring
    August 21, 2007
    So your booking table contains "available" rooms not "booked" rooms?

    Validate the form field dates and either pass the values using cfqueryparam or using STR_TO_DATE(form.arrivalDate, theDateFormat) . For performance reasons its usually better to use a function on a constant value instead of a column, when possible.
    P
    paross1
    Participating Frequently
    August 21, 2007
    Just a hunch, but I would guess that your database columns are date/time objects and your form variables are text strings (or vice versa). Or, your comparing one date/time object with another without accounting for the time componnent, so it would be impossible for you to use = and get a match, etc. However, I can't tell for sure based on what you have posted, as it is not obvious what the data types are for your database columns and form variables.

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices