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 further your understanding of 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 displayed each row of data from the Employee table, but the information was unformatted. You can use HTML tables to control the layout of information on the page. In addition, you can use CFML functions to format individual pieces of data, such as dates and numeric values.

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.

To put the query results in a table:

Return to the file actionpage.cfm in ColdFusion Studio.

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='#Form.LastName#'

</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>

<cfoutput>Contractor: #Form.Contractor#</cfoutput>

</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>
Put data into a table.

<tr> <th>First Name</th> <th>Last Name</th> <th>Salary</th> </tr>
In the first row of the table, include three columns, with the headings: First Name, Last Name, and Salary.

<cfoutput query="GetEmployees">
Get ready to display the results of the GetEmployees query.

<tr> <td>#FirstName#</td> <td>#LastName#</td> <td>#Salary#</td> </tr>
Create a new row in the table, with three columns. For a record, put the value of the FirstName field, the value of the LastName field, and the value of the Salary field.

</cfoutput>
Keep getting records that matches the criteria, and display each row in a new table row until you run out of records.

</table>
End of table.

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

Formatting individual data items

You might want to 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 ColdFusion Studio.

Change the line

<td>#Salary#</td>

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

Performing pattern matching

Use the SQL LIKE operator and SQL wildcard strings in a SQL WHERE clause when you want to compare a value against a character string field so that the query returns database information based on commonalities. This technique is known as pattern matching and is often used to query databases.

For example, to return data for employees whose last name starts with AL, you build a query that looks like this:

<cfquery name="GetEmployees" datasource="CompanyInfo">

  SELECT FirstName, LastName,

  StartDate, Salary, Contract

  FROM    Employee

  WHERE   LastName LIKE 'AL%'

</cfquery>

The LIKE operator tells the database to use the string that follows for pattern matching.
If you place a wildcard before and after AL, you retrieve any record in that column that contains AL.
Surround strings in SQL statements with single quotes (').

To return information from the Departmt table on all departments except the sales department, you would build a query that looks like this:

<cfquery name="GetDepartments" datasource="CompanyInfo">

  SELECT *

  FROM    Departmt

  WHERE   Dept_Name NOT LIKE '[Ss]ales'

</cfquery>

The first character in the match can be either upper case S or lower case s.

Note

Whether SQL identifiers and data comparison operations are case sensitive depends on the database.

Filtering data based on multiple conditions

When you want to retrieve data based on the results of more than one comparison you can use AND and OR operators to combine conditions.

For example, to return data for contract employees who earn more than $50,000, would build a query that looks like this:

<cfquery name="GetEmployees" datasource="CompanyInfo">

  SELECT FirstName, LastName,

  StartDate, Salary, Contract

  FROM    Employee

  WHERE   Contract = 'Yes'

  AND     Salary > 50000 

</cfquery>

Creating table joins

Many times, the data that you want to retrieve is maintained in multiple tables. For example, in the database that you are working with:

Department information is maintained in the Departmt table. This includes department ID numbers.
Employee information is maintained in the Employee table. This also includes department ID numbers.

To compare and retrieve data from more than one table during a query, use the WHERE clause to join two tables through common information.

For example, to return employee names, start dates, department names, and salaries for employees who work for the HR department, you build a query that looks like this:

<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

  AND Departmt.Dept_Name = 'HR'

</cfquery>

In this example, the following criteria joins the two tables:

Departmt.Dept_ID = Employee.Dept_ID

It ensures that each row of the query results contains the department name from the Departmt table that corresponds to the Department ID in this employee's row in the Employee table. Without this statement, the query returns a row for every employee in the Employee table, and all rows have the Dept_Name HR, even if the employee is not in the HR department.

When you do table joins, keep the following information in mind:

Prefix each column in the SELECT statement to explicitly state which table the data should be retrieved from.
In this example, the Dept_ID field is the primary key of the Departmt table and the foreign Key of the Employee table. A foreign key uniquely identifies another record (in this example, a record in the Departmt table) but does not uniquely identify the current record (in the Employee table).

Building flexible search interfaces

If you want users to optionally enter multiple search criteria, you can wrap conditional logic around the SQL AND clause to build a flexible search interface. To test for multiple conditions, wrap additional cfif tags around additional AND clauses.

The following action page allows users to search for employees by department, last name, or both.

To build a more flexible search interface:

Open the page actionpage.cfm in ColdFusion Studio.

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 = '#Form.Department#'

  </cfif>

  <cfif Form.LastName IS NOT "">

    AND Employee.LastName = '#Form.LastName#'

  </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
Retrieve 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 = '#Form.Department#' </cfif>
If the user specified a department on the form, only retrieve records where the department name is the same as the one the user specified. Note that you need the 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 = '#Form.LastName#' </cfif>
If the user specified a last name in the form, only retrieve the records in which the last name is the same as the one the user entered in the form.

Developing ColdFusion Applications
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	Retrieve 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 = '#Form.Department#' </cfif>	If the user specified a department on the form, only retrieve records where the department name is the same as the one the user specified. Note that you need the 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 = '#Form.LastName#' </cfif>	If the user specified a last name in the form, only retrieve the records in which the last name is the same as the one the user entered in the form.