Displaying the Results of a Selection
Once you've created a CFQUERY in your application page file, you can then reference its results within other CFML tags. The query results can then be used to dynamically create an HTML page.

As you learn to use CFML tags in application page files, keep in mind that you can also use HTML tags and text in application page files. Wherever you use standard HTML tags and text inside your application page, ColdFusion simply passes the tags and text directly back to the client browser.

The most flexible way to display data retrieved from a CFQUERY is to define a CFML output section in your application page file using the CFOUTPUT tag. Output sections can be linked to a specific query or contain content from multiple queries.

Basic output sections have the following syntax:

     <CFOUTPUT QUERY="queryname" MAXROWS=n STARTROW=s>
        Literal text, HTML tags, and 
        dynamic field references (e.g., #FirstName#)
     </CFOUTPUT>

Note the use of the pound signs (#) to enclose the naked variable name FirstName. A CFOUTPUT tag can contain:

• Literal text
• HTML tags
• References to query columns
• References to dynamic parameters such as form fields
• Functions

Example
If you execute a CFQUERY called EmployeeList and you want to use it to display the first name, last name, and e-mail address of each employee (separated by a horizontal rule), use the following CFOUTPUT section:

EmployeeList2.cfm

     <CFOUTPUT QUERY="EmployeeList">
        <HR>
        #FirstName# #LastName# 
        (E-mail: <A HREF="mailto:#Email#">#Email#</A>)<BR>
     </CFOUTPUT>

If there were three records in the query result set, the HTML generated by this CFOUTPUT section would look like this:

   <HR>
   Deborah Jones 
   (E-mail: <A HREF="mailto:dj@ibm.net">dj@ibm.net</A>)<BR>
   <HR>
   John Smith 
   (E-mail: <A HREF="mailto:js@ibm.net">js@ibm.net</A>)<BR>
   <HR>
   Frank Wilson 
   (E-mail: <A HREF="mailto:fw@ibm.net">fw@ibm.net</A>)<BR>

Notice the use of the standard HTML <A HREF="...> tag with the mailto: URL to give a live e-mail link on the page.

Presenting Query Output in a Table
Presenting the results of queries using CFOUTPUT sections is usually adequate if the number of records returned is small. However, you might need a more compact and structured display of query results. Because the CFOUTPUT tag can include any HTML, you can use standard HTML table tags to build a table dynamically. The result is a clear, concise rendering of your query results.

Let's change our above example to display it in a table:

EmployeeList3.cfm

   <TABLE>
      <TR>
      <TD>First Name</TD>
      <TD>Last Name</TD>
      <TD>E-mail</TD></TR>
   <CFOUTPUT QUERY="EmployeeList" MAXROWS=10>
      <TR>
      <TD>#FirstName#</TD>
      <TD>#LastName#</TD>
      <TD><A HREF="mailto:#Email#">#Email#</A></TD></TR>
   </CFOUTPUT>
   </TABLE>

This code would create a table with three columns labeled "FirstName", "Last Name" and "E-mail". The table would draw its data from the CFQUERY named "EmployeeList" and would display no more than 10 rows.

   <TABLE>
      <TR>
      <TD>First Name</TD>
      <TD>Last Name</TD>
      <TD>E-mail</TD>
      </TR>
      <TR>
      <TD>John</TD>
      <TD>Smith</TD>
      <TD><A HREF="mailto:js@ibm.net" js@ibm.net</A></TD> 
      <TR>
      ...
   </TABLE>

In your browser you should see something like:

First Name	Last Name	E-mail
Deborah	Jones	dj@ibm.net
John	Smith	js@ibm.net
Frank	Wilson	fw@ibm.net

Using Dynamic Parameters in SQL Statements
You can harness the real power of the CFQUERY tag by dynamically customizing the contents of the SQL attribute by using parameters passed to the application page. A SQL statement is customized by embedding dynamic parameters within the SQL text. Dynamic parameters (also called variables) normally include form entries, parameters passed in the URL, and CGI environment information.

The convention for including a dynamic parameter inside a SQL statement is to enclose it in pound (#) signs (e.g., #LastName#). Whenever ColdFusion sees text enclosed by # signs, it searches through all Form, URL, cookies, client, and CGI variables looking for one that matches the specified name. When it finds the name, it substitutes the appropriate value for the parameter reference. If you specify the variable type (Form) used in the following example less searching will have to be done and your code will run a bit faster.

Example of dynamic SQL
If you created a form to allow end users to search for employees by last name, you could use the following SQL statement with dynamic parameters:

   <CFQUERY NAME=&quot;EmployeeList&quot;
      DATASOURCE=&quot;CompanyDB&quot;>
      SELECT * FROM Employees 
      WHERE LastName = '#Form.LastName#'
   </CFQUERY>

If a user entered "Rucker" for LastName, the SQL statement sent to the database would be:

	SELECT * FROM Employees 
 	WHERE LastName = 'Rucker'

Sources for dynamic parameters
The following table summarizes the primary sources from which you can draw dynamic parameters for use in your SQL queries:

Field	Description
Form fields	The most common way of passing parameters to an application page. When a user enters data in a form field, a parameter bearing the name of the form field (#Form.formfield#) is passed to the application page.
URL parameters	Parameters that are embedded on the end of a URL (such as, /input.cfm?name=adam).
Server	A variable that remains available to all application pages until the ColdFusion application server terminates.
CGI environment	An environment variable interpreted by the browser. Every request sent to an application page has several environment variables sent to it that relate to the context in which it was sent. The variables available depend on the browser and server software in use for a given request.
Query objects	Query columns you can reference once a query has been executed. Once a query has been run, its results can be used as dynamic parameters in other queries. For example, a query that returns a column called UserID can be Referenced in the following form: queryname.UserID
Cookies	General mechanism for storing and retrieving information about the Web client (browser)
Client variables	Used to store persistent client variables in the system Registry on the Web server. These variables are specific to an individual browser accessing your ColdFusion application.
Session variables	Variables available only for an individual session. Session Variables are tied to an individual client and persist for as long as that Client ID maintains a session.
Application variables	Variables available only for an individual application. Application names are defined in the CFAPPLICATION tag, which is typically used in the application.cfm file.

Testing and Debugging Applications