Skip to main content
S
seasonedweb
Inspiring
November 17, 2013
Question

Flexible Criteria Search?

  • November 17, 2013
  • 1 reply
  • 2086 views

Greetings

I contsructed a form that allows the user to search up to five criteria:

Date range,

appraiser,

order status,

client

priority

I've attempted to set the action page:

<cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

SELECT order_ID, order_number, order_client_ID, order_rush_status_ID, main_orders.order_status_ID, order_appraiser_ID, order_create_date, order_property_street, order_property_city, client_ID, client_company, appraiser_ID, appraiser_fname, appraiser_lname, lookup_order_status.order_status_ID, order_status, rush_status_ID, rush_status

FROM  main_orders MO, lookup_clients LC, lookup_appraisers LA, lookup_order_status LS, lookup_rush_status LR

WHERE 1 = 1

<cfif IsDefined("Form.StartDate")>

<cfset Session.StartDate = "#CreateODBCDate(FORM.StartDate)#">

<cfset Session.EndDate = "#CreateODBCDate(FORM.EndDate)#">

AND (order_create_date BETWEEN #Session.StartDate# AND #Session.EndDate#)</cfif>

<cfif IsDefined("Form.order_client_ID") AND Form.order_client_ID NEQ 0>

AND    client_ID  = #Form.order_client_ID# </cfif>

<cfif IsDefined("Form.order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

  AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

 

 

<cfif IsDefined("Form.order_status_ID") AND Form.order_status_ID NEQ 0>

   AND   MO.order_status_ID = #Form.order_status_ID#

   </cfif>

  

<cfif IsDefined("Form.order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

    AND  rush_status_ID = #Form.order_rush_status_ID#

    </cfif>

ORDER BY order_create_date DESC

</cfquery>  

The first error encountered is "AND  rush_status_ID = #Form.order_rush_status_ID#"

Not yet got this to work- any advice would be greatly appreciated.

Thanks

seasonedweb

This topic has been closed for replies.

1 reply

Carl Von Stetten
Carl Von Stetten
Legend
November 18, 2013

There are a few problems I see with your query:

  1. Your FROM clause lists 5 tables, but no join statements to tie them together (or alternately, any WHERE clause statements that tie them together).  You need to add some INNER or LEFT JOINs to make it work.
  2. You've assigned aliases to the tables (MO, LC, LA, LS, LR), but you don't use them in identifying where the columns in your SELECT and WHERE clause are coming from.  Prefix each column name with the appropriate alias (e.g.: MO.order_ID).
  3. You should use StructKeyExists() instead of IsDefined() for performance reasons.  So the first one would be <cfif StructKeyExists(Form, "StartDate")>.
  4. You should also check to make sure Form.EndDate is provided as well.
  5. Unless there is a reason you need to do this in some other place in your code, you don't need to put the ODBCDates into session.  In fact, depending on the format of the date input field in your form, you can use CFQueryParam to make sure the date is passed to the database in a valid format.
  6. **USE CFQUERYPARAM**!!  Whenever you pass values to a query that come from a source you can't absolutely trust not to be a hacking attempt (especially form fields), ALWAYS use CFQueryParam to prevent SQL Injection.  This will also make sure numbers, strings, and dates are passed to the database in the proper formats.  It also can improve query performance as the database can reuse the query plan from previous times the query has run, even if the values passed to the query are changing.

If after addressing all of these issues the query continues to throw errors, post your updated query definition and we'll take another look.

HTH,

-Carl V.

S
seasonedwebAuthor
Inspiring
November 21, 2013

Carl:

Based on your feedback, I did this:

<cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

SELECT order_ID, order_number, order_client_ID, order_rush_status_ID, main_orders.order_status_ID, order_appraiser_ID, order_create_date, order_property_street, order_property_city, client_ID, client_company, appraiser_ID, appraiser_fname, appraiser_lname, lookup_order_status.order_status_ID, order_status, rush_status_ID, rush_status

FROM  main_orders, lookup_clients, lookup_appraisers, lookup_order_status, lookup_rush_status

WHERE order_client_ID = client_ID

AND order_appraiser_ID = appraiser_ID

AND main_orders.order_status_ID = lookup_order_status.order_status_ID

AND order_rush_status_ID = rush_status_ID

<cfif StructKeyExists(Form, "StartDate")>

<cfset StartDate = "#FORM.StartDate#">

<cfset EndDate = "#FORM.EndDate#">

AND (order_create_date BETWEEN #StartDate# AND #EndDate#)</cfif>

<cfif StructKeyExists(Form, "order_client_ID") AND Form.order_client_ID NEQ 0>

AND client_ID  = #Form.order_client_ID# </cfif>

<cfif StructKeyExists(Form, "order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

<cfif StructKeyExists(Form, "order_status_ID") AND Form.order_status_ID NEQ 0>

 

AND order_status_ID = #Form.order_status_ID#</cfif>

<cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

AND  rush_status_ID = #Form.order_rush_status_ID#</cfif>

ORDER BY order_create_date DESC

</cfquery>  

My query results are:

<cfset nowtime = now()>

<cfoutput query="search_flexible_criteria">

<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

<td width="8%" class="tddynamic">#DateDiff("d", order_create_date, nowtime)# </td>

<td width="10%" class="tddynamic">#DateFormat(order_create_date, "mm/dd/yyyy")#</td>

<td width="8%" class="tddynamic">#order_number#</td>

<td width="15%" class="tddynamic">#order_property_street# #order_property_city#</td>

<td width="12%" class="tddynamic">#order_status#</td>

<td width="8%" class="tddynamic">#rush_status#</td>

<td width="10%" class="tddynamic">#appraiser_lname#, #appraiser_fname#</td>

</tr>

</cfoutput>

Is this closer to the solution (other than not using cfqueryparam)?

Thanks again

Norman B.

Carl Von Stetten
Carl Von Stetten
Legend
November 22, 2013

Norman,

Inside your CFQUERY tag you should still make use of table aliases (it looks like you actually removed them entirely this time), or prefix each mention of a database column with the full table name (using aliases requires much less typing though).  This is a good habit to get into, as it is not uncommon for multiple tables to have identically named columns.  Without prefixing the columns with the table name or alias, the database will throw an error about ambiguous column names.

Is this new query you provided working as expected now?  Or is it still throwing errors?

-Carl V.

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