Building Queries

As discussed earlier in this chapter, you build queries using the cfquery tag and SQL.

To query the table:

Create a new application page in ColdFusion Studio.

Edit the page so that it appears as follows:

<html>

<head>

<title>Employee List</title>

</head>

<body>

<h1>Employee List</h1>

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

  SELECT FirstName, LastName, Salary, Contract

  FROM Employee

</cfquery>

</body>

</html>

Save the page as emplist.cfm in myapps under the Web root directory. For example, the directory path on your machine might be:
C:\INETPUB\WWWROOT\myapps on Windows NT
Return to your browser and enter the following URL to view EmpList.cfm:
```
http://127.0.0.1/myapps/emplist.cfm
```
View the source in the browser.
The ColdFusion Server creates the EmpList data set, but only HTML and text is sent back to the browser so you just see the heading "Employee List". To display the data set on the page, you must code tags and variables to output the data.

Reviewing the code

The query you just created retrieves data from the CompanyInfo database. The following table describes the code and its function:

Code

Description

<cfquery name="EmpList" datasource="CompanyInfo">
Queries the database specified in the CompanyInfo data source

SELECT FirstName, LastName, Salary, Contract FROM Employee
Gets information from the FirstName, LastName, Salary, and Contract fields in the Employee table

</cfquery>
Ends the cfquery block

Code	Description
<cfquery name="EmpList" datasource="CompanyInfo">	Queries the database specified in the CompanyInfo data source
SELECT FirstName, LastName, Salary, Contract FROM Employee	Gets information from the FirstName, LastName, Salary, and Contract fields in the Employee table
</cfquery>	Ends the `cfquery` block

Query notes and considerations

When creating queries to retrieve data, keep the following guidelines in mind:

Enter the query name and datasource attributes in the begin cfquery tag.
Surround attribute settings with double quotes(").
Make sure that a data source exists in the ColdFusion Administrator before you reference it n a cfquery tag. Alternatively, use the dbtype = "dynamic" and queryString attributes to dynamically specify a database.
The SQL that you write is sent to the database and performs the actual data retrieval.
Columns and tables that you refer to in your SQL statement must exist, otherwise the query will fail.
Reference the query data by naming the query in one of the presentation tags, such as cfoutput, cfgrid, cftable, cfgraph, or cftree later on the page.

Developing ColdFusion Applications
Querying a Database