The Cold Fusion Web Database Construction Kit
In Chapter 10, "Cold Fusion Basics," you built various Cold Fusion templates to enable your users to browse an employee list. In this chapter, you expand this application to enable users to search for specific data.
HTML supports basic forms, which are screens that collect data from users via their Web browsers. You can use these forms to collect data to be sent to Cold Fusion for processing. In following chapters that use HTML forms, you will use the basic forms skills you learn in this chapter.
In Chapter 10, you learned how to create Cold Fusion templates to dynamically display data retrieved from ODBC data sources. The A2Z Employee table has just 10 rows in it, so the data fits easily within a Web browser window.
But what do you do if you have hundreds or thousands of rows? Displaying all that data in one long list is impractical. Scrolling through lists of names to find the one you want just doesn't work well. The solution is to enable users to search for names by specifying what they are looking for. You can allow them to enter a first name, a last name, or part of a name, and then you can display only the employee records that meet the search criteria.
To accomplish this solution, you need to do two things. First, you need to create your search form using the HTML form tags. Then you need to create a template that builds SQL SELECT statements dynamically based on the data collected and submitted by the form.
See the "Creating Queries" section in Chapter 8 for a detailed discussion of SQL statements and specifically the SQL SELECT statement.
Before you can create a search form, you need to learn how Cold Fusion interacts with HTML forms. Listing 11.1 contains the code for a sample form that prompts for a first and last name. Create this template, and save it in the C:\A2Z\SCRIPTS directory as FORM1.CFM.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 1</TITLE> </HEAD> <BODY> <FORM ACTION="forms2.cfm" METHOD="POST"> Please enter your name and then click <B>Process</B>. <P> First name: <INPUT TYPE="text" NAME="FirstName"> <BR> Last name: <INPUT TYPE="text" NAME="LastName"> <BR> <INPUT TYPE="submit" VALUE="Process"> </FORM> </BODY> </HTML>
Next, in your browser, type the URL http://yourserver.com/ a2z/hello1.cfm to display the form as shown in Figure 11.1.
This form is simple, with just two data entry fields and a submit button, but it clearly demonstrates how forms are used to submit data to Cold Fusion.
Figure 11.1 You can use HTML forms to collect data to be submitted to Cold Fusion.
You create HTML forms by using the <FORM> tag. <FORM> usually takes two parameters passed as tag attributes. The ACTION attribute specifies the name of the script or program that the Web server should execute in response to the form's submission. To submit a form to Cold Fusion, you simply specify the name of the Cold Fusion template that will process the form. The following example specifies that the template forms2.cfm should process the submitted form:
ACTION="forms2.cfm"
The METHOD attribute specifies how data is sent back to the Web server. All Cold Fusion forms must be submitted as type POST.
CAUTION: The default submission type is not POST; it is usually GET. If you omit the ACTION="POST" attribute from your form tag, Cold Fusion returns an error message and does not process your form correctly.
Your form has only two data entry fields. <INPUT TYPE="text" NAME="FirstName"> and <INPUT TYPE="text" NAME="LastName"> both create simple text fields. The NAME attribute in the INPUT tag specifies the name of the field, and Cold Fusion uses this name to refer to the field when it is processed.
Each form in a field is typically given a unique name. If two fields have the same name, then both sets of values are returned to be processed, separated by a comma. Usually, you want to be able to validate and manipulate each field individually, so each field should have its own name. The notable exceptions are the check box and radio button input types, which are described later in this chapter.
The last item in the form is an INPUT type of submit. The submit input type creates a button that, when clicked, submits the form contents to the Web server for processing. Almost every form has a submit button (or a graphic image that, when clicked, acts like a submit button). The VALUE attribute specifies the text to display within the button, so <INPUT TYPE="submit" VALUE="Process"> creates a submit button with the text Process in it.
TIP: When you're using an INPUT type of submit, you should always specify button text by using the VALUE attribute. If you don't, the default text Submit Query (or something similar) is displayed, and this text is likely to confuse your users.
If you enter your name into the fields and submit the form right now, you receive a Cold Fusion error message like the one shown in Figure 11.2. This error says that template C:\A2Z\SCRIPTS\FORMS2.CFM cannot be found.
Figure 11.2 Cold Fusion returns an error message when it cannot process your request.
This error message, of course, is perfectly valid. You submitted a form to be passed to Cold Fusion and processed with a template, but you have not created that template yet. So, your next task is to create a template to process the form submission.
To demonstrate how to process returned forms, you need to create a simple template that echoes back the name you enter. The template is shown in Listing 11.2.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 2</TITLE> </HEAD> <BODY> <CFOUTPUT> Hello #FirstName# #LastName# </CFOUTPUT> </BODY> </HTML>
By now the CFOUTPUT tag should be familiar to you; you use it to mark a block of code that Cold Fusion should parse and process. The line Hello #FirstName# #LastName# is processed by Cold Fusion. #FirstName# is replaced with the value you entered into the FirstName field, and #LastName# is replaced with the value in the LastName field.
See Chapter 10 for a detailed discussion of the Cold Fusion CFOUTPUT tag.
Create a template called FORMS2.CFM containing the code in Listing 11.2, and save it in the C:\A2Z\SCRIPTS directory. Then resubmit your name by clicking the form's submit button once again. This time, you should see a browser display similar to the one shown in Figure 11.3. Whatever name you enter into the first and last name fields appears.
Figure 11.3 Using Cold Fusion, you can manipulate and control form fields.
Other input types that you will frequently use are check boxes and option buttons. Check boxes are used to select options that have just one of two states: on or off, yes or no, and true or false. To ask a user if he or she wants to be notified via e-mail of book availability, for example, you create a check box field. If the user selects the box, his or her name is added to the mailing list; if the user does not select the box, then his or her name is not added.
Option buttons are used to select one of at least two mutually exclusive options. You can implement a field prompting for payment type with options such as Cash, Check, Credit card, or P.O., for example, as an option button field.
The code example in Listing 11.3 creates a form that uses both option buttons and check box fields.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 3</TITLE> </HEAD> <BODY> <FORM ACTION="forms4.cfm" METHOD="POST"> Please fill in this form and then click <B>Process</B>. <P> Payment type:<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Cash">Cash<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Check">Check<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Credit card">Credit card<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="P.O.">P.O. <P> Would you like to be added to our mailing list? <INPUT TYPE="checkbox" NAME="MailingList"> <P> <INPUT TYPE="submit" VALUE="Process"> </FORM> </BODY> </HTML>
Figure 11.4 shows how this form appears in your browser.
Figure 11.4 You can use input types of option buttons and check boxes to facilitate the selection of options.
Now, before you create FORMS4.CFM to process this form, you should note a couple of important points. First, look at the four lines of code that make up the Payment Type option button selection. Each one contains the exact same NAME attribute, NAME="PaymentType". Clearly, the four input fields have the same name so that your browser knows that they are part of the same field. If each option button has a separate name, then the browser does not know that these buttons are mutually exclusive and thus allows the selection of more than one button.
Another important point is that, unlike INPUT type text, option buttons have no associated text or data entry area. Therefore, for the browser to associate a particular value with each option button, you must use the VALUE attribute. The code VALUE="Cash" instructs the browser to return the value Cash in the PaymentType field if that button is selected.
So, now that you can accept option button and check box fields, you're ready to create a template to process them. Create a template called FORMS4.CFM in the C:\A2Z\SCRIPTS directory using the template code in Listing 11.4.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 4</TITLE> </HEAD> <BODY> <CFOUTPUT> Hello,<BR> You selected <B>#FORM.PaymentType#</B> as your payment type.<BR> <CFIF #FORM.MailingList# CONTAINS "on"> You will be added to our mailing list. <CFELSE> You will not be added to our mailing list. </CFIF> </CFOUTPUT> </BODY> </HTML>
The form processing code in Listing 11.4 displays the payment type you select. The field PaymentType is fully qualified with the FORM field type to prevent name collisions.
See the "Specifying Field Types" section in Chapter 10 for an explanation of name collisions and how to avoid them.
When a check box is selected, your browser returns the text on, indicating that the option is turned on. Rather than display the text on in your form response, you create a conditional response using the <CFIF> tag. The code <CFIF #FORM.MailingList# CONTAINS "on"> instructs Cold Fusion to process the following code only if the MailingList field contains "on" or is selected. If the field does not contain "on," then the code following <CFELSE> is used instead.
See the "Using Conditions" section in Chapter 12 for a complete explanation of the <CFIF> tag and its use.
Now load form FORMS3.CFM on your browser, select a payment option, and then select the check box. Next, click the Process button. Your browser display should look like the one shown in Figure 11.5.
Figure 11.5 You can use Cold Fusion templates to process user-selected options.
Well, that process worked exactly as intended, so now get ready to complicate things a little. Reload template FORMS3.CFM, and submit it without selecting a payment type or with the MailingList field not selected. As a result, Cold Fusion generates an error message, as shown in Figure 11.6. The field you do not select generates a "Form Field Not Found" error.
Figure 11.6 Option buttons or check boxes that are submitted with no value generate a Cold Fusion error.
Check the code in Listing 11.3 to verify that the form fields do in fact exist. So, why does Cold Fusion report that the form field does not exist? Well, this is one of the quirks of HTML forms. If you select a check box, the on value is submitted; if you do not select the check box, nothing is submitted, not even an empty field. The same is true of option buttons: if you make no selection, then the field is not submitted at all. (This behavior is the exact opposite of the text INPUT type, which returns empty fields as opposed to no field.)
So how do you work around this limitation? Well, you can choose from two solutions. You can modify your form processing script to check which fields exist by using the #ParameterExists()# function and, if the field exists, process it.
A simpler solution is to prevent the browser from omitting fields that are not selected. You can modify the option button field so that one option is preselected. The users cannot avoid making a option button selection, so they have to make a selection or use the preselected options. To preselect an option button, just add the attribute CHECKED to one of the buttons.
The check box field is trickier. Because you have only one field, you cannot set one as CHECKED as you can a option button. The solution is to embed a hidden field. Hidden fields are not displayed in the browser, but they are returned when the form is submitted. If you create a hidden field with the same name as the check box field and set the value to off, the value off is returned if you do not select the check box. If, however, the check box is selected, then the value off,on is returned. Because you have two fields with the same name, both values are returned, and you can check to see if the returned value contains the word on. For this reason, you create the condition as <CFIF #FORM.MailingList# CONTAINS "on"> in Listing 11.4, not <CFIF #FORM.MailingList# IS "on">. The IS condition fails if you use the hidden field technique because the value could never be on. It can only be off or off,on. Using <CFIF #FORM.MailingList# CONTAINS "on">, you can check to see if the value on is contained within the returned value.
The updated form is shown in Listing 11.5. The first option button in the PaymentType field is modified to read <INPUT TYPE="radio" NAME="PaymentType" VALUE="Cash" CHECKED>. The CHECKED attribute ensures that a button is checked. You also add the line <INPUT TYPE="hidden" NAME="MailingList" VALUE="off"> to create a hidden field so that a value always appears in the MailingList, thus ensuring it is always returned when the form is submitted.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 5</TITLE> </HEAD> <BODY> <FORM ACTION="forms4.cfm" METHOD="POST"> Please fill in this form and then click <B>Process</B>. <P> Payment type:<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Cash" CHECKED>Cash<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Check">Check<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="Credit card">Credit card<BR> <INPUT TYPE="radio" NAME="PaymentType" VALUE="P.O.">P.O. <P> Would you like to be added to our mailing list? <INPUT TYPE="hidden" NAME="MailingList" VALUE="off"> <INPUT TYPE="checkbox" NAME="MailingList"> <P> <INPUT TYPE="submit" VALUE="Process"> </FORM> </BODY> </HTML>
Create and save this template as C:\A2Z\SCRIPTS\FORMS5.CFM. Then try using it and experiment with the two fields. You'll find that this form is reliable and robust, and it does not generate Cold Fusion error messages.
Another field type that you will frequently use is the list box. Using list boxes is an efficient way to enable users to select one or more options. If a list box is created to accept only a single selection, then you can be guaranteed that a value is always returned. If you don't set one of the options to be preselected, then the first one in the list is selected. An option always has to be selected.
List boxes that allow multiple selections also allow no selections at all. If you use a multiple selection list box, you once again have to find a way to ensure that Cold Fusion does not generate "Form Field Not Found" errors.
Listing 11.6 contains the same data entry form that you just created but replaces the option buttons with a list box. Save this template as C:\A2Z\SCRIPTS\FORMS6.CFM, and then test it with your browser.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 6</TITLE> </HEAD> <BODY> <FORM ACTION="forms4.cfm" METHOD="POST"> Please fill in this form and then click <B>Process</B>. <P> Payment type: <SELECT NAME="PaymentType"> <OPTION>Cash <OPTION>Check <OPTION>Credit card <OPTION>P.O. </SELECT> <P> Would you like to be added to our mailing list? <INPUT TYPE="hidden" NAME="MailingList" VALUE="off"> <INPUT TYPE="checkbox" NAME="MailingList"> <P> <INPUT TYPE="submit" VALUE="Process"> </FORM> </BODY> </HTML>
For this particular form, the browser display shown in Figure 11.7 is probably a better user interface. The choice of whether to use option buttons or list boxes is yours, and no hard and fast rules exist as to when to use one versus the other. The following guidelines, however, may help you determine which to use:
Figure 11.7 You can use HTML list boxes to select one or more options.
Text area fields are boxes in which the users can enter free-form text. When you create a text area field, you specify the number of rows and columns of screen space it should occupy. This area, however, does not restrict the amount of text that users can enter. The field scrolls both horizontally and vertically to enable the users to enter more text.
Listing 11.7 creates an HTML form with a text area field for user comments. The field's width is specified as a number of characters that can be typed on a single line; the height is the number of lines that are displayed without scrolling.
TIP: The TEXTAREA COLUMN attribute is specified as a number of characters that can fit on a single line. This setting is dependent on the font in which the text is displayed, and the font is browser specific. Make sure that you test any TEXTAREA fields in more than one browser because a field that fits nicely in one might not fit at all in another.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 7</TITLE> </HEAD> <BODY> <FORM ACTION="forms8.cfm" METHOD="POST"> Please enter your comments in the box provided, and then click <B>Send</B>. <P> <TEXTAREA NAME="Comments" ROWS="6" COLUMNS="40"></TEXTAREA> <P> <INPUT TYPE="submit" VALUE="Send"> </FORM> </BODY> </HTML>
Listing 11.8 contains Cold Fusion code to display the contents of a TEXTAREA field.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 8</TITLE> </HEAD> <BODY> <CFOUTPUT> Thank you for your comments. You entered: <P> <B>#FORM.Comments#</B> </CFOUTPUT> </BODY> </HTML>
Figure 11.8 shows the TEXTAREA field you created, and Figure 11.9 shows how Cold Fusion displays the field.
Figure 11.8 The HTML TEXTAREA field is a means by which you can accept free-form text input from users.
Try entering line breaks (press the Enter key) in the text field, and submit it. What happens to the line breaks? Line break characters are considered white-space characters, just like spaces, by your browser, and all white space is ignored by browsers. "WHITE SPACE IS IGNORED" is displayed no differently than "WHITE SPACE IS IGNORED."
Figure 11.9 Without Cold Fusion output functions, TEXTAREA fields are not displayed with line breaks preserved.
The only way to display line breaks is to replace the line break with an HTML paragraph tag, the <P> tag. You, therefore, have to parse through the entire field text and insert <P> tags wherever needed. Fortunately, Cold Fusion makes this task a simple one. The Cold Fusion #ParagraphFormat()# function automatically replaces every double line break with a <P> tag. (Single line breaks are not replaced because Cold Fusion has no way of knowing if the next line is a new paragraph or part of the previous one.)
The code in Listing 11.9 contains the same comments form as the one in Listing 11.7, with two differences. First, default field text is provided. Unlike other INPUT types, <TEXTAREA> default text is specified between <TEXTAREA> and </TEXTAREA> tags, and not in a VALUE attribute.
Second, you use the WRAP attribute to wrap text entered into the field automatically. WRAP="VIRTUAL" instructs the browser to wrap to the next line automatically, just as most word processors and editors do.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 9</TITLE> </HEAD> <BODY> <FORM ACTIONforms10.cfm" METHOD="POST"> Please enter your comments in the box provided, and then click <B>Send</B>. <P> <TEXTAREA NAME="Comments" ROWS="6" COLUMNS="40" WRAP="VIRTUAL"> Replace this text with your comments. </TEXTAREA> <P> <INPUT TYPE="submit" VALUE="Send"> </FORM> </BODY> </HTML>
NOTE: The TEXTAREA WRAP attribute is not supported by many browsers. These browsers ignore the attribute and require the users to enter line breaks manually. Because the attribute is ignored when not supported, you can safely use this option when necessary, and your forms do not become incompatible with older browsers.
Listing 11.10 shows the template to display the user-supplied comments. The Comments field code is changed to #ParagraphFormat(FORM.Comments)#, ensuring that all line breaks are maintained and displayed correctly, as shown in Figure 11.10.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 10</TITLE> </HEAD> <BODY> <CFOUTPUT> Thank you for your comments. You entered: <P> <B>#ParagraphFormat(FORM.Comments)#</B> </CFOUTPUT> </BODY> </HTML>
Figure 11.10 You should use the Cold Fusion ParagraphFormat() function to display TEXTAREA fields with their line breaks preserved.
The HTML forms specification supports only two types of buttons. Almost all forms, including all the forms that you create in this chapter, have a submit button. Submit, as its name implies, instructs the browser to submit the form fields to a Web server.
The second supported button type is reset. Reset clears all form entries. Any text entered into INPUT TYPE="text" or TEXTAREA fields is cleared, as are any check box, list box, and option button selections. Many forms have reset buttons, but you never need more than one.
On the other hand, you may want more than one submit button. For example, if you're using a form to modify a record, you could have two submit buttons: one for Update and one for Delete. (Of course, you also could use two forms to accomplish this task.) If you create multiple submit buttons, you must name the button with the NAME attribute and make sure to assign a different VALUE attribute for each. The code in Listing 11.11 contains a reset button and two submit buttons.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 11</TITLE> </HEAD> <BODY> <FORM ACTION="forms12.cfm" METHOD="POST"> <P> First name: <INPUT TYPE="text" NAME="FirstName"> <BR> Last name: <INPUT TYPE="text" NAME="LastName"> <P> <INPUT TYPE="submit" NAME="Operation" VALUE="Update"> <INPUT TYPE="submit" NAME="Operation" VALUE="Delete"> <INPUT TYPE="reset" VALUE="Clear"> </FORM> </BODY> </HTML>
The result of this code is shown in Figure 11.11.
Figure 11.11 When you're using multiple submit buttons, you must assign a different value to each button.
When you name submit buttons, you treat them as any other form field. Listing 11.12 demonstrates how to determine which submit button was clicked. The code <CFIF #FORM.Operation# IS "Update"> checks to see if the Update button was clicked, and <CFELSEIF #FORM.Operation# IS "Delete"> checks to see if Delete was clicked only if Update was not clicked.
<HTML> <HEAD> <TITLE>Learning Cold Fusion Forms 12</TITLE> </HEAD> <BODY> <CFOUTPUT> <CFIF #FORM.Operation# IS "Update"> You opted to <B>update</B> #FirstName# #LastName# <CFELSEIF #FORM.Operation# IS "Delete"> You opted to <B>delete</B> #FirstName# #LastName# </CFIF> </CFOUTPUT> </BODY> </HTML>
Now that you're familiar with forms and how Cold Fusion processes them, you can return to creating an employee search screen. The first screen to create enables users to search for an employee by last name. To begin, you need an INPUT field of type text. The field name can be anything you want, but using the same name as the table column to which you're comparing the value is generally a good idea.
TIP: When you're creating search screens, you can name your form fields with any descriptive name you want. When you're creating insert and update forms, however, the field name must match the table column names so that Cold Fusion knows which field to save with each column. For this reason, you should get into the habit of always naming form fields with the appropriate table column name.
The code in Listing 11.13 contains a simple HTML form, not unlike the test forms you created earlier in this chapter. The form contains a single text field called LastName and a submit button.
<HTML> <HEAD> <TITLE>Employee Search</TITLE> </HEAD> <BODY> <H2>Please enter the last name to search for.</H2> <FORM ACTION="empsrch2.cfm" METHOD="POST"> Last name: <INPUT TYPE="text" NAME="LastName"><BR> <P> <INPUT TYPE="submit" VALUE="Search"> </FORM> </BODY> </HTML>
Save this form as C:\A2Z\SCRIPTS\EMPSRCH1.CFM, and then go to it with your browser. Your display should look like the one shown in Figure 11.12.
Figure 11.12 On the employee search screen, users can search for employees by last name only.
The FORM ACTION attribute specifies which Cold Fusion template should be used to process this search. The code ACTION="empsrch2.cfm" instructs Cold Fusion to use the template EMPSRCH2.CFM, which is shown in Listing 11.14. Create this template, and save it as C:\A2Z\SCRIPTS\EMPSRCH2.CFM.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
WHERE LastName LIKE `#LastName#%'
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER=5>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
<A HREF="empdtl2.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A>
</TD>
<TD>
Ext. #PhoneExtension#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</CENTER>
</BODY>
</HTML>
The template begins with a CFQUERY tag that specifies the ODBC data source, the SQL statement to execute, and the name Cold Fusion should use to refer to the results set.
The WHERE clause in Listing 11.14 contains a Cold Fusion field rather than a static value. When Cold Fusion parses templates, it replaces field names with the value contained within the field. So, if you search for all last names beginning with Sm, the code WHERE LastName LIKE `#LastName#%' becomes WHERE LastName LIKE `Sm%'. If no search text is specified at all, the clause becomes WHERE LastName LIKE `%', a wild-card search that finds all records.
See the "Creating Queries" section in Chapter 8 for a detailed discussion of SQL statements and specifically the SQL SELECT statement.
See the "Creating an ODBC Data Source" section in Chapter 8 for a complete discussion of ODBC data sources and how to create and maintain them.
See the "The CFQUERY Tag" section in Chapter 10 for an explanation of the use of the Cold Fusion CFQUERY tag.
You use a LIKE clause to enable users to enter partial names. The clause WHERE LastName = `Sm' finds only employees whose last names are Sm. Users with a last name of Smith are not retrieved. Using a wild card, as in the clause WHERE LastName LIKE `Sm%', enables users to search on partial names, too.
Try experimenting with different search strings. The sample output should look like the output shown in Figure 11.13. Of course, depending on the search criteria you specify, you'll see different search results.
Figure 11.13 By building WHERE clauses dynamically, you can create different search conditions on-the-fly.
Listing 11.15 contains the code for template C:\A2Z\SCRIPTS\EMPDTL2.CFM, which is an updated version of the employee detail template you created in the preceding chapter. The template is changed to enable users to send e-mail directly to employees by clicking their e-mail addresses. You make this action possible by using the mailto identifier, which instructs the browser to open an e-mail window so that the users can enter mail messages to be sent to the selected address. If employee Kim Black is selected, the code <A HREF="mailto:#EMail#">#EMail#</A> expands to <A HREF="mailto:kblack@a2z books.com">kblack@a2zbooks.com</A>, and anyone can click that address to send Kim e-mail. Of course, to prevent errors, the code is conditional, based on an e-mail address being present. <CFIF #EMail# IS NOT ""> evaluates to true only if the EMail field is not empty.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employee"
>
SELECT LastName,
FirstName,
MiddleInit,
Title,
PhoneExtension,
PhoneCellular,
PhonePager,
EMail
FROM Employees
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
<HTML>
<CFOUTPUT QUERY="Employee">
<HEAD>
<TITLE>#LastName#, #FirstName# #MiddleInit#</TITLE>
</HEAD>
<BODY>
<H1>#LastName#, #FirstName#</H1>
<HR>
Title: #Title#
<BR>
Extension: #PhoneExtension#
<BR>
Cellular: #PhoneCellular#
<BR>
Pager: #PhonePager#
<BR>
E-Mail: <CFIF #EMail# IS NOT ""><A HREF="mailto:#EMail#">#EMail#</A></CFIF>
</BODY>
</CFOUTPUT>
</HTML>
When you roll out your employee search screen, immediately you are inundated with requests. "Searching by last name is great, but what about first name or phone extension?" your users ask. Now that you have introduced the ability to search for data, your users want to be able to search on several different fields, too.
Adding fields to your search screen is simple enough. So, add two fields: one for first name and one for phone extensions. The code for the updated employee search screen is shown in Listing 11.16.
<HTML> <HEAD> <TITLE>Employee Search</TITLE> </HEAD> <BODY> <H2>Please enter your search text below</H2> <FORM ACTION="empsrch4.cfm" METHOD="POST"> First name: <INPUT TYPE="text" NAME="FirstName"><BR> Last name: <INPUT TYPE="text" NAME="LastName"><BR> Extension: <INPUT TYPE="text" NAME="PhoneExtension"> <P> <INPUT TYPE="submit" VALUE="Search"> <INPUT TYPE="reset" VALUE="Clear"> </FORM> </BODY> </HTML>
This form enables the users to specify text in one of three different fields, as shown in Figure 11.4.
Figure 11.14 The employee search screen is used to locate employee records by name or part thereof.
Before you can actually perform a search, you need to create the search template. The complete search code is shown in Listing 11.17.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
<CFIF #FirstName# IS NOT "">
WHERE FirstName LIKE `#FirstName#%'
<CFELSEIF #LastName# IS NOT "">
WHERE LastName LIKE `#LastName#%'
<CFELSEIF #PhoneExtension# IS NOT "">
WHERE PhoneExtension LIKE `#PhoneExtension#%'
</CFIF>
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER=5>
<CFOUTPUT>
<TR>
<TH COLSPAN=2>
<H3>Found #Employees.RecordCount# Employees</H3>
</TH>
</TR>
</CFOUTPUT>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
<A HREF="empdtl2.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A>
</TD>
<TD>
Ext. #PhoneExtension#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</CENTER>
</BODY>
</HTML>
Now, before you actually perform a search, take a closer look at the template in Listing 11.17. The CFQUERY tag is similar to the one you used in the previous search template, but in this one, the SQL SELECT statement in the SQL attribute is incomplete. It does not specify a WHERE clause with which to perform a search, nor does it specify a search order. No WHERE clause is specified because the search screen has to support not one but four search types, as follow:
How can a single search template handle all these possible search conditions? The answer is dynamic SQL.
When you're creating dynamic SQL statements, you break up the statement into separate common SQL and specific SQL. The common SQL is the part of the SQL statement that you always want. The sample SQL statement has two: the SELECT FirstName, LastName, PhoneExtension, EmployeeID FROM Employees and the ORDER BY LastName, FirstName.
If no search criteria is provided, then the common text is all the SQL statement you need. If, however, search text is specified, then the number of possible WHERE clauses is endless.
To understand the process of creating dynamic SQL statements, take another look at Listing 11.17. The code <CFIF #FirstName# IS NOT ""> checks to see that the FirstName form field is not blank. If no text is entered into the FirstName field in the search form, this condition fails, and any code until the next <CFELSE> <CFELSEIF> or </CFIF> is ignored.
See the "Using Conditions" section in Chapter 12 for a complete explanation of the CFIF tag and its use.
If a value does appear in the FirstName field, the code WHERE FirstName LIKE `#FirstName#%' is processed and appended to the SQL statement. #FirstName# is a field and is replaced with whatever text is entered into the FirstName field. So, if John is specified as the first name to search for, this statement translates to WHERE FirstName LIKE `John%'. This text is appended to the previous SQL statement, which now becomes the following:
SELECT FirstName, LastName, PhoneExtension, EmployeeID FROM Employees WHERE FirstName LIKE `John%'
All you need now is the ORDER BY clause. Even though the ORDER BY is fixed and does not change with different searches, it must still be built dynamically because the ORDER BY clause must come after the WHERE clause, if one exists. After Cold Fusion processes the code ORDER BY LastName, FirstName, the finished SQL statement reads as follows:
SELECT FirstName, LastName, PhoneExtension, EmployeeID FROM Employees WHERE FirstName LIKE `John%' ORDER BY LastName, FirstName
See the "LIKE" section in Chapter 8 for an explanation of the LIKE condition and the % wild-card character.
NOTE: You cannot use double quotation marks in an SQL statement. When Cold Fusion encounters a double quotation mark, it thinks that it has reached the end of the SQL statement. It then generates an error message because extra text appears where Cold Fusion thinks it should not be. To include text strings with the an SQL statement, use only single quotation marks.
Similarly, if a last name of Sm is specified as the search text, the complete SQL statement reads as follows:
SELECT FirstName, LastName, PhoneExtension, EmployeeID FROM Employees WHERE LastName LIKE `Sm%' ORDER BY LastName, FirstName
The code <CFIF #FirstName# IS NOT ""> evaluates to false because #FirstName# is actually empty, so Cold Fusion checks the next condition, <CFIF #LastName# IS NOT "">. This condition evaluates to true because a last name value is provided. Cold Fusion then processes the next line, the CFSQL statement, and builds the required SELECT statement.
Now that the template is complete and all the code to build dynamic SQL is in place, try to perform a search. First, try a search without specifying any search criteria at all. Your browser display should look like the one shown in Figure 11.15.
Figure 11.15 With dynamic SQL, you can use a single template to perform an infinite number of searches.
Notice that the number of employees found is displayed at the top of the table. Cold Fusion stores the number of rows retrieved with a query in a field called RecordCount. To determine how many rows the Employees query retrieves, you use the code Found #Employees.RecordCount# Employees, and just like any other field, Cold Fusion replaces it with the actual value.
The RecordCount field is also useful for returning messages, or options, if a search returns no data at all. For example, the following code displays an informative message that no records are retrieved:
<CFIF #Employees.RecordCount# IS 0>No employees located, try changing your search criteria.</CFIF>
Try performing different searches using the search form. You can search on any of the fields to retrieve specific records.
Now try entering text in both the first name and last name fields. What happens? The answer is not much at all. The dynamic SQL code processes only the first search criteria it encounters. If it finds text in the first name field, it does not even check the last name and phone extension fields. If you follow the flow of the <CFIF> statement, you see that as soon as any condition is true, either a <CFIF> or a <CFELSEIF>, Cold Fusion ceases processing the condition and jumps to the </CFIF> tag.
So how can you search on more than one field? Well, creating a WHERE clause with an unknown number of conditions is more complicated than the WHERE clauses you created earlier. If a particular condition is the first condition in the clause, you need to precede it with WHERE. If it is not the first condition, you need to precede it with an AND. Of course, you have no idea if any fields will be specified. Nor do you know which fields will be specified, if any are. So how do you construct a dynamic SQL statement like this?
One solution is shown in Listing 11.18. The SQL SELECT statement has a dummy WHERE clause, WHERE EmployeeID = EmployeeID. This condition checks to see if the EmployeeID in a retrieved record matches itself, which, of course, it always does. For example, when the employee with ID 6 is retrieved, the database checks to see if 6=6. Because only one column is being checked, and the condition involves no complicated arithmetic or subqueries, the check has almost no performance penalty. If no search criteria is specified, then the WHERE clause remains WHERE EmployeeID = EmployeeID, and all records are retrieved.
Right after the CFQUERY are three sets of <CFIF> conditions. Each one checks to see if a specific search field is provided, and if so, it is appended to the SQL SELECT statement. So if Kim is entered into the first name field, the new WHERE clause is WHERE EmployeeID = EmployeeID AND FirstName LIKE `Kim%'. This clause correctly filters out only employees whose first name begins with Kim.
Now see what happens if a second field is specified, for example, B in the last name field. The <CFIF #LastName# IS NOT ""> evaluates to true because LastName is not empty, and the AND LastName LIKE `#LastName#%' code is added from the LastName filter to the WHERE clause. The new WHERE clause reads WHERE EmployeeID = EmployeeID AND FirstName LIKE `Kim%' AND LastName LIKE `B%'. If you execute this search, only Kim Black is found.
To try this code example, save Listing 11.18 as C:\A2Z\EMPSRCH5.CFM. You also need to modify the FORM ACTION attribute in template EMPSRCH3.CFM so that it specifies EMPSRCH5.CFM as the destination template instead of EMPSRCH4.CFM.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
WHERE EmployeeID = EmployeeID
<CFIF #FirstName# IS NOT "">
AND FirstName LIKE `#FirstName#%'
</CFIF>
<CFIF #LastName# IS NOT "">
AND LastName LIKE `#LastName#%'
</CFIF>
<CFIF #PhoneExtension# IS NOT "">
AND PhoneExtension LIKE `#PhoneExtension#%'
</CFIF>
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER=5>
<CFOUTPUT>
<TR>
<TH COLSPAN=2>
<H3>Found #Employees.RecordCount# Employees</H3>
</TH>
</TR>
</CFOUTPUT>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
<A HREF="empdtl2.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A>
</TD>
<TD>
Ext. #PhoneExtension#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</CENTER>
</BODY>
</HTML>
After you create the template, use your browser to try performing different combinations of searches. You'll find that this new search template is both powerful and flexible. Indeed, this technique for creating truly dynamic SQL SELECT statements will likely be the basis for some sophisticated database interaction in real-world applications.
The more power and flexibility you give your users, the more they want. Now they want to search by department, too. For example, they may want to display all the sales department personnel or find a particular employee by specifying a name within a specific department.
The search screen is simple enough to create. A list of departments is the perfect place to use a list box. You can create a list box with the HTML <SELECT> tag and then list all the departments as <OPTION> tags within it.
Before you modify the search template, however, remember that you're creating data-driven applications. You don't want to have to enter the departments manually in the list box. Rather, you want the list box to be driven by the data in the Departments table. This way, when departments are added or if a department name changes, you can acquire those changes automatically.
The code in Listing 11.19 demonstrates a data-driven form. The CFQUERY at the top of the template should be familiar to you by now. It creates a result set called Departments that contains the ID and name of each department in the database.
The body of the form is essentially the same as the one you created in Listing 11.16, with the exception of the new DepartmentID field. DepartmentID is a list box that displays the names of all the departments. The <SELECT> tag creates the list box, and it is terminated with the </SELECT> tag. The individual entries in the list box are specified with the <OPTION> tag, but here that tag is within a CFOUTPUT block. This block is executed once for each row retrieved by the CFQUERY, creating an <OPTION> entry for each one.
The code in Listing 11.19 demonstrates the process of building data driven forms.. The SELECT NAME attribute contains the name of the field, which is the same as the column in the Employee table that you need to compare against. The CFQUERY block creates the individual options, using the ID field as the VALUE and the department name as the description. When Cold Fusion processes department 2, the Sales department, the code <OPTION VALUE="#id#">#Department# is translated into <OPTION VALUE="2">Sales.
Also notice that you need to include a blank <OPTION> line in the list box. You need this empty option because you need to be able to not make a selection. Remember that list boxes always must have a selection. Save Listing 11.19 as C:\A2Z\SCRIPTS\EMPSRCH6.CFM.
<
CFQUERY
DATASOURCE="A2Z"
NAME="Departments"
>
SELECT ID, Department
FROM Departments
ORDER BY Department
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee Search</TITLE>
</HEAD>
<BODY>
<H2>Please enter your search text below</H2>
<FORM ACTION="empsrch7.cfm" METHOD="POST">
First name: <INPUT TYPE="text" NAME="FirstName"><BR>
Last name: <INPUT TYPE="text" NAME="LastName"><BR>
Extension: <INPUT TYPE="text" NAME="PhoneExtension"><BR>
Department:
<SELECT NAME="DepartmentID">
<OPTION>
<CFOUTPUT QUERY="Departments">
<OPTION VALUE="#id#">#Department#
</CFOUTPUT>
</SELECT>
<P>
<INPUT TYPE="submit" VALUE="Search">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
The completed search screen is shown in Figure 11.16.
Figure 11.16 Search forms should use a mixture of input types to create a user-friendly interface.
Now you have one last thing left to do: You need to modify the search template to include the new DepartmentID field. The code in Listing 11.20 is updated to include one more possible WHERE condition. The code <CFSQL SQL=" AND DepartmentID = #DepartmentID#"> adds the DepartmentID field only if it is specified in the search screen, thereby enabling users to search on as many or as few fields as they want.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
WHERE EmployeeID = EmployeeID
<CFIF #FirstName# IS NOT "">
AND FirstName LIKE `#FirstName#%'
</CFIF>
<CFIF #LastName# IS NOT "">
AND LastName LIKE `#LastName#%'
</CFIF>
<CFIF #PhoneExtension# IS NOT "">
AND PhoneExtension LIKE `#PhoneExtension#%'
</CFIF>
<CFIF #DepartmentID# IS NOT "">
AND DepartmentID = #DepartmentID#
</CFIF>
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER=5>
<CFOUTPUT>
<TR>
<TH COLSPAN=2>
<H3>Found #Employees.RecordCount# Employees</H3>
</TH>
</TR>
</CFOUTPUT>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
<A HREF="empdtl2.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A>
</TD>
<TD>
Ext. #PhoneExtension#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</CENTER>
</BODY>
</HTML>
The final WHERE condition in Listing 11.20 is different from the prior three in two ways. First, the condition checks for equality instead of LIKE. The value you're comparing is an ID value, a number, and numbers either match or don't match. Wild cards don't apply to numbers.
Second, no quotation marks appear around the field identifier. The prior three fields all require quotation marks because they are text values. The DepartmentID is a number, and numbers don't need quotation marks around them. In fact, if you do put quotation marks around the number, you generate an ODBC error because you're comparing a numeric field to a string.
This chapter introduced Cold Fusion forms. Cold Fusion greatly extends the capabilities of the HTML form tags by enabling you to collect and manipulate form data.
You learned the different HTML form input types and how Cold Fusion interacts with each of them. You also learned how to use forms as a front end to data queries and how to build dynamic SQL statements that are driven by form fields. Finally, you learned about data-driven forms, which enable you to create completely dynamic data-driven applications.
For more information about topics mentioned in this chapter, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.