Working with queries and data

The ability to generate and display query data is one of the most important and flexible features of ColdFusion. The following sections describe more about using queries and displaying their results. Some of these tools are effective for presenting any data, not just query results.

Using HTML tables to display query results

You can use HTML tables to specify how the results of a query appear on a page. To do so, you put the cfoutput tag inside the table tags. You can also use the HTML th tag to put column labels in a header row. To create a row in the table for each row in the query results, put the tr block inside the cfoutput tag.

In addition, you can use CFML functions to format individual pieces of data, such as dates and numeric values.

To put the query results in a table:

Open the ColdFusion page actionpage.cfm in your editor.

Modify the page so that it appears as follows:

<html>
<head>
<title>Retrieving Employee Data Based on Criteia from Form</title>
</head>

<body>
<cfquery name="GetEmployees" datasource="CompanyInfo">
  SELECT FirstName, LastName, Salary
  FROM Employee
  WHERE LastName=<cfqueryparam value="#Form.LastName#" 
CFSQLType="CF_SQL_VARCHAR">
</cfquery>
<h4>Employee Data Based on Criteria from Form</h4>
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Salary</th>
</tr>
<cfoutput query="GetEmployees">
<tr>
<td>#FirstName#</td>
<td>#LastName#</td>
<td>#Salary#</td>
</tr>
</cfoutput>
</table>
<br>
<cfif IsDefined("Form.Contractor")>
  <cfoutput>Contractor: #Form.Contractor#</cfoutput>
</cfif>
</body>
</html>

Save the page as actionpage.cfm within the myapps directory.
View formpage.cfm in your browser.
Enter Smith in the Last Name text box and submit the form.
The records that match the criteria specified in the form appear in a table.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

<table>

Puts data into a table.

<tr> <th>First Name</th> <th>Last Name</th> <th>Salary</th> </tr>

In the first row of the table, includes three columns, with the headings: First Name, Last Name, and Salary.

<cfoutput query="GetEmployees">

Gets ready to display the results of the GetEmployees query.

<tr> <td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td> </tr>

Creates a new row in the table, with three columns. For a record, puts the value of the FirstName field, the value of the LastName field, and the value of the Salary field.

</cfoutput>

Keeps getting records that matches the criteria, and displays each row in a new table row until you run out of records.

</table>

End of table.

Code	Description
<table>	Puts data into a table.
<tr> <th>First Name</th> <th>Last Name</th> <th>Salary</th> </tr>	In the first row of the table, includes three columns, with the headings: First Name, Last Name, and Salary.
<cfoutput query="GetEmployees">	Gets ready to display the results of the GetEmployees query.
<tr> <td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td> </tr>	Creates a new row in the table, with three columns. For a record, puts the value of the FirstName field, the value of the LastName field, and the value of the Salary field.
</cfoutput>	Keeps getting records that matches the criteria, and displays each row in a new table row until you run out of records.
</table>	End of table.

Formatting individual data items

You can format individual data items. For example, you can format the Salary field as a monetary value. To format the Salary using the dollar format, you use the CFML expression DollarFormat(number).

To change the format of the Salary:

Open the file actionpage.cfm in your editor.

Change the following line:

<td>#Salary#</td>

<td>#DollarFormat(Salary)#</td>

Save the page.

Building flexible search interfaces

One option with forms is to build a search based on the form data. For example, you could use form data as part of the WHERE clause to construct a database query.

To give users the option to enter multiple search criteria in a form, you can wrap conditional logic around a SQL AND clause as part of the WHERE clause. The following action page allows users to search for employees by department, last name, or both.

Note: ColdFusion provides the Verity search utility that you can also use to perform a search. For more information, see Chapter 24, "Building a Search Interface".

To build a more flexible search interface:

Open the ColdFusion page actionpage.cfm in your editor.

Modify the page so that it appears as follows:

<html>
<head>
<title>Retrieving Employee Data Based on Criteia from Form</title>
</head>
<body>
<cfquery name="GetEmployees" datasource="CompanyInfo"> 
  SELECT Departmt.Dept_Name,
    Employee.FirstName,
    Employee.LastName,
    Employee.StartDate,
    Employee.Salary
  FROM Departmt, Employee
  WHERE Departmt.Dept_ID = Employee.Dept_ID
  <cfif IsDefined("Form.Department")>
AND Departmt.Dept_Name=<cfqueryparam value="#Form.Department#" 
      CFSQLType="CF_SQL_VARCHAR"> 
  </cfif>
  <cfif Form.LastName IS NOT "">
    AND Employee.LastName=<cfqueryparam value="#Form.LastName#" 
      CFSQLType="CF_SQL_VARCHAR">
  </cfif>
</cfquery>

<h4>Employee Data Based on Criteria from Form</h4>
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Salary</th>
</tr>
<cfoutput query="GetEmployees">
<tr>
<td>#FirstName#</td>
<td>#LastName#</td>
<td>#Salary#</td>
</tr>
</cfoutput>
</table>
</body>
</html>

Save the file.
View formpage.cfm in your browser.
Select a department, optionally enter a last name, and submit the form.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

SELECT Departmt.Dept_Name, Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID

Retrieves the fields listed from the Departmt and Employee tables, joining the tables based on the Dept_ID field in each table.

<cfif IsDefined("FORM.Department")> AND Departmt.Dept_Name = <cfqueryparam value="#Form.Department#" CFSQLType="CF_SQL_VARCHAR"> </cfif>

If the user specified a department on the form, only retrieves records where the department name is the same as the one the user specified. You must use pound signs in the SQL AND statement to identify Form.Department as a ColdFusion variable, but not in the IsDefined function.

<cfif Form.LastName IS NOT ""> AND Employee.LastName = <cfqueryparam value="#Form.LastName#" CFSQLType="CF_SQL_VARCHAR"> </cfif>

If the user specified a last name in the form, only retrieves the records in which the last name is the same as the one the user entered in the form.

Developing ColdFusion MX Applications with CFML
Retrieving and Formatting Data

Code	Description
SELECT Departmt.Dept_Name, Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID	Retrieves the fields listed from the Departmt and Employee tables, joining the tables based on the Dept_ID field in each table.
<cfif IsDefined("FORM.Department")> AND Departmt.Dept_Name = <cfqueryparam value="#Form.Department#" CFSQLType="CF_SQL_VARCHAR"> </cfif>	If the user specified a department on the form, only retrieves records where the department name is the same as the one the user specified. You must use pound signs in the SQL AND statement to identify Form.Department as a ColdFusion variable, but not in the IsDefined function.
<cfif Form.LastName IS NOT ""> AND Employee.LastName = <cfqueryparam value="#Form.LastName#" CFSQLType="CF_SQL_VARCHAR"> </cfif>	If the user specified a last name in the form, only retrieves the records in which the last name is the same as the one the user entered in the form.

Working with queries and data

Using HTML tables to display query results

To put the query results in a table:

Reviewing the code

Formatting individual data items

To change the format of the Salary:

Building flexible search interfaces

To build a more flexible search interface:

Reviewing the code

Comments