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.
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.
<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>
The following table describes the highlighted code and its function:
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)
.
<td>#Salary#</td>
to
<td>#DollarFormat(Salary)#</td>
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".
<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>
The following table describes the highlighted code and its function: