Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

In this case, the variables name and score are column names from the database containing the names and exam scores. When flanked with pound signs and enclosed in <CFOUTPUT> and </CFOUTPUT> tags, the values of these variables are printed instead. And because you used the QUERY attribute of the <CFOUTPUT> tag, ColdFusion will automatically loop over the entire query set and produce a table row for each name and exam score. Imagine how long it would take you to code such a table for a class of 30 students! With ColdFusion, you can produce the output table with just a few lines of code.

Sometimes query result sets can be rather long, and it’s helpful to limit the amount of output the user gets at one time. To assist with this, you can use the MAXROWS attribute of the <CFOUTPUT> tag to specify a maximum number of rows from the result set that the tags should loop over. You might set MAXROWS to 20, for example, so that a user only sees, at most, 20 records at a time.

You can also control the record where the output begins by using the <CFOUTPUT> tag’s START attribute. This enables you to set up a link pretty easily to see the next 20 records. All you need to do is pass the value of the last record printed, add one to it, and set the START attribute equal to that value.

Performing Database Operations

ColdFusion’s strength lies in its capability to communicate with any ODBC-compliant database and retrieve, store, update, or delete records in those databases. Regardless of the type of database operation you’re doing, you use the <CFQUERY> and </CFQUERY> tags to enclose the SQL statement that does the operation. The next four sections look at how to use <CFQUERY> for the basic operations of retrieving, inserting, updating, and deleting.

Retrieving Database Records Querying a database to find records that match specific search criteria is probably the most popular of the fundamental database operations. ColdFusion makes each part of this task easy—from incorporating search criteria entered on an HTML form into the SQL statement, to performing the actual query, to receiving and printing out the results of the query in HTML format.

Suppose you are assigned to create an intranet page for your company’s sales force that prints a list of your clients in a state that the user can specify. To begin, you would set up a form that asks the user for which state they want a list. The HTML for that form might look like this:

<FORM ACTION=“statelist.cfm” METHOD=“POST”>
<B>Show me clients in the following state:</B><P>
<SELECT NAME=“state”>
<OPTION VALUE=“AL”>Alabama
<OPTION VALUE=“AK”>Alaska
<OPTION VALUE=“AR”>Arkansas
...
<OPTION VALUE=“WY”>Wyoming
</SELECT>
<INPUT TYPE=“SUBMIT” VALUE=”Generate List”>
</FORM>

This produces a drop-down list of all the states. The user chooses one of them and then clicks the Generate List button to submit the request to the server. Seeing an ACTION attribute that ends in .cfm (the file extension for ColdFusion templates), the server hands over the template and the form data to the ColdFusion Application Server for processing.

The challenge now is to write the template called statelist.cfm so that it

1.  Queries the database for clients located in the state the user selects.

2.  Produces an HTML page that will display the client listing.

Both of these are easy to accomplish with CFML. First, you need to write a query that pulls client information based on the selected state. You can do this with the following code:

<CFQUERY DATASOURCE=“clients” NAME=“ByState”>
SELECT company, city
FROM Clients
WHERE state = ‘#Form.state#’
</CFQUERY>

Note first that <CFQUERY> takes a few attributes. The DATASOURCE attribute is mandatory and should be set equal to the name of a data source you set up in the ColdFusion Administrator. The NAME attribute is also required and enables you to assign a unique name to the query so you can reference the results set by that name.

Next, note the WHERE clause in the SQL statement. Instead of a specific state’s name, you see #Form.state#. ColdFusion treats submitted form data as an object that you can reference by the name Form. To key in on a particular form field, you can say Form.field_name. That’s why you see Form.state in the code above—it references the state field of the form that was submitted. By enclosing Form.state in pound signs, you instruct ColdFusion to substitute in the value it finds at Form.state. So in this case, if the user chose the state of California, the resulting SQL statement (after ColdFusion substitutes in the value) would be

SELECT company, city, state
FROM Clients
WHERE state = ‘CA’

In response to this SQL statement, the database would return the company name and city of each client in the state of California and store the result set under the name “ByState”. You can then use the <CFOUTPUT> tag with the QUERY attribute to produce a table that prints out the entire list of clients. The complete listing for this template is shown in Listing 34.1.

Listing 34.1 statelist.cfm Code Listing

<CFQUERY DATASOURCE=“clients” NAME=“ByState”>
SELECT company, city
FROM Clients
WHERE state = ‘#Form.state#’
</CFQUERY>

<HTML>

<HEAD>
<TITLE>Client Listing for <CFOUTPUT>#Form.state#</CFOUTPUT></TITLE>
</HEAD>

<BODY BGCOLOR=“WHITE”>

<H1>Client Listing for <CFOUTPUT>#Form.state#</CFOUTPUT></H1>

<CFOUTPUT>#ByState.RecordCount#</CFOUTPUT> records found
<P>
<TABLE>
<TR><TH>Company</TH><TH>Location</TH></TR>
<CFOUTPUT QUERY=“ByState”>
<TR><TD>#company#</TD><TD>#city#, #Form.state#</TD></TR>
</CFOUTPUT>
</TABLE>

</BODY>

</HTML>