ColdFusion MX: From Static to Dynamic in 10 Steps

By Barry Moore

In the next example, we are going to customize our employee search form to include two new search fields: first name and last name. When people use the form for searching, they will be able to search by an employee's first name (or the first few letters), last name (or the first few letters), and department (see Figure 5.9).

Creating the form and getting it to pass on the information is straightforward enough, but what about the action page? Our form page might be passing on any combination of the three fields from the search form. Consequently, our <CFQUERY> block on the action page might need to have one, two, or three different criteria in the WHERE clause, depending on which fields the user did or did not fill in.

We need to create what is commonly referred to as a dynamic query. This means that the query structure will change depending on what is passed by the form page. This can be done with the use of <CFIF> statements within our <CFQUERY> block. Look at the following query:

<CFQUERY NAME="qMyQuery" DATASOURCE="MyDSN"> SELECT Field1, Field2, Field3 FROM Table1 WHERE Field1 = '#FORM.Field1Search#' AND Field2 = '#FORM.Field2Search#' AND Field3 = '#FORM.Field3Search#' </CFQUERY>

This code would work just fine if we could be sure that the user would enter search information into all three search fields. However, users might enter information into just two fields, just one field, or none at all. Our query has to be able to take this into account and continue to work.

We can use CFIF statements in our query block to determine whether a form variable passed by the form page actually contains any data. The logic would be as follows: If the field does contain data, use it in the query; if not, ignore it. Look at the following amended query code:

<CFQUERY NAME="qMyQuery" DATASOURCE="MyDSN"> SELECT Field1, Field2, Field3 FROM Table1 <!--- check for information in the FORM fields ---> <CFIF FORM.Field1Search IS NOT "" > WHERE Field1 = '#FORM.Field1Search#' </CFIF> <CFIF FORM.Field2Search IS NOT "" > AND Field2 = '#FORM.Field2Search#' </CFIF> <CFIF FORM.Field2Search IS NOT "" > AND Field3 = '#FORM.Field3Search#' </CFIF> </CFQUERY>

This query block uses <CFIF> statements to check whether each form variable passed by the form page is blank. If it is not blank (it actually contains some data), we include the variable in our query criteria. If the variable does not contain any data, we skip it.

This query will work fine if Field1Search information is always filled out on the search page. If a user leaves the Field1Search field blank on the form page and submits the form, our IF statement will see that the FORM.Field1Search variable is empty (equal to "") and will ignore that portion of the query statement. Unfortunately, that line contains our WHERE clause. Without that, our query will break, and we will get a big fat error message.

Because we can never be sure which combination of fields will be filled out, we cannot accurately determine which field to use to start off our WHERE clause. An easy way to remedy this is with a dummy WHERE clause criterion. A dummy WHERE clause criterion is something that will always evaluate to TRUE but that does not have any effect on the actual query results. Look at the following query:

<CFQUERY NAME="qMyQuery" DATASOURCE="MyDSN"> SELECT Field1, Field2, Field3 FROM Table1 <!--- use a dummy WHERE clause criterion ---> WHERE 0=0 <!--- check for information in the FORM fields ---> <CFIF FORM.Field1Search IS NOT "" > AND Field1 = '#FORM.Field1Search#' </CFIF> <CFIF FORM.Field2Search IS NOT "" > AND Field2 = '#FORM.Field2Search#' </CFIF> <CFIF FORM.Field2Search IS NOT "" > AND Field3 = '#FORM.Field3Search#' </CFIF> </CFQUERY>

In this example, we are using a dummy WHERE clause criterion of WHERE 0=0 to start off our WHERE clause. Because 0 will always equal 0, this part of the WHERE clause is always true. We then just add on whatever fields are not blank by using <CFIF> statements and the SQL AND keyword. Bada-boom, bada-bing, everything works fine. If a user leaves all three search fields blank, our query will just run the WHERE 0=0 clause and will return all the records in Table1.