Creating dynamic websites with ColdFusion
Part 2: Displaying data with SQL
by Michael Smith
What is ColdFusion
In this article we continue to look at what ColdFusion is
and how you can use it for dynamic website creation. We cover displaying data
from any database and testing and debugging techniques. We also mention where
you can learn more about ColdFusion, including the free CPCUG ColdFusion
Conference in June.
In case you missed the last article that introduced
ColdFusion, let me explain what it is. ColdFusion is a programming language
based on standard HTML (Hyper Text Meta Language) that is used to write dynamic
webpages. It lets you create pages on the fly that differ depending on user
input, database lookups, time of day or what ever other criteria you dream up!
ColdFusion pages consist of standard HTML tags such as <FONT SIZE=”+2”>
together with CFML (ColdFusion Meta Language) tags such as <CFQUERY>,
and <CFLOOP>. ColdFusion was introduced by Allaire in 1996
and is currently on version 4.0
Creating a Page to Select and Display Data
To select and output data from a database, you create a
ColdFusion application page that uses the CFQUERY tag, plus any HTML formatting
you want. The database query is done with SQL (Structured Query Language)
inside the CFQUERY tag.
To illustrate, here is an example from a database called
Company associated with an ODBC (Open DataBase Connectivity) data source named
To create a select query called EmployeeList that retrieves
all of the records in the Employees table, use this syntax:
FirstName, LastName, PhoneNumber, Email
SQL and ODBC
SQL is an industry standard language used to interact with
relational databases. While the advanced features of SQL are extremely
powerful, learning the basics of SQL is relatively simple. You can learn the SQL
you need to know to use ColdFusion productively in a few minutes.
The basic SQL statements are:
SELECT FieldsList FROM TableName WHERE conditions
ORDER BY FieldsList
INSERT INTO TableName (FieldsList) VALUES (ValuesList)
DELETE * FROM TableName WHERE conditions
UPDATE TableName SET field = value WHERE condition
In practice, you rarely need to write SQL statements by hand
since there are so many high-quality visual SQL tools available. Visual SQL
tools include the Microsoft Access query builder or the Microsoft Query applet
bundled with Microsoft Office. These tools allow you to visually define and
preview queries and then copy and paste the resulting SQL statement into other applications
such as ColdFusion. This will save you a lot of cryptic ODBC error messages and
SQL syntax errors too!
By the way, ODBC is a way for programs such as ColdFusion to
use the same SQL with different backend databases without having to change any
code. The ODBC driver handles all the details of how to talk to the particular
database used and you are free to spend more time on other issues such as the
user interface. ODBC drivers are available for most databases including:
Comma delimited text
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 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 any 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
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.
sections have the following syntax:
<CFOUTPUT QUERY="queryname" MAXROWS=n STARTROW=s>
Literal text, HTML tags, and
dynamic field references (e.g. #FirstName#)
the use of the pound signs (#) to enclose the naked variable name FirstName.
A CFOUTPUT tag can contain:
References to query columns
References to dynamic parameters like form fields
If you execute a CFQUERY called EmployeeList and you want to
use it to display the first name, last name, and email address of each employee
(separated by a horizontal rule), use the following CFOUTPUT section:
(Email: <A HREF="mailto:#Email#">#Email#</A>)
If there were three records in the query result set, the
HTML generated by this CFOUTPUT section would look like this:
HREF="mailto:[email protected]">[email protected]</A>)<BR>
HREF="mailto:[email protected]">[email protected]</A>)<BR>
(Email: <A HREF="mailto:[email protected]">[email protected]</A>)<BR>
Notice the use of the standard HTML <A HREF> tag with
the mailto: URL to give a live email 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
Let’s change our above example to display in table:
This code creates a table with three columns labeled
"FirstName," "Last Name" and "Email”. The table draws
its data from the CFQUERY named "EmployeeList" and shows no more than
HREF=”mailto:[email protected]” [email protected]</A></TD>
In your browser you should see something like:
Using Dynamic Parameters in SQL Statements
You can harness the real power of the CFQUERY tag when you
dynamically customize the contents of the SQL attribute by using parameters
passed to the application page. The 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) in our example
below then less searching has 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
* FROM Employees
WHERE LastName =
If the user
entered "Rucker" for LastName, the SQL statement sent to the database
SELECT * FROM Employees
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:
Variable Types in CF
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
Parameters that are embedded on the end of a URL (such as, /input.cfm?name=adam).
A variable that remains available to all application pages
until the ColdFusion application server terminates.
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 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
General mechanism for storing and retrieving information
About the Web client (browser).
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
Variables available only for
an individual session. Session
Variables are tied to an individual client and persist for
long as that Client ID maintains a session.
Variables available only for an individual application.
Application names are defined in the CFAPPLICATION tag, which is typically
used in the application.cfm file.
To Learn More
If you are interesting in learning more about ColdFusion
CPCUG and TeraTech are holding a free ColdFusion User Conference on Saturday
6/26/99 at the Masur Auditorium from 9am to 6pm. The morning will contain
introductory sessions and the afternoon advanced ones. You can sign up for the conference
or call 301-424-3903.
You can download a free 30 day-evaluation version of
ColdFusion from Allaire or request a free eval CD-ROM from
the Allaire website http://www.allaire.com/
1 Alewife Center
Cambridge, MA 02140
Tel: 617.761.2000 voice
Fax: 617.761.2001 fax
Toll Free: 888.939.2545
Email: [email protected]
ColdFusion costs $1295 direct from Allaire. TeraTech is
offering a 10% discount for all CPCUG members and their organizations. Call
301-424-3903 for this
SQL Help and Tutorial -
Tutorial - Using Microsoft Access-SQL - http://www.cs.jcu.edu.au/Subjects/cp1500/1999/tutes/AccessSqlEx4-2.html
SQL Reference -
SQL Reference (Microsoft) -
Sams Teach Yourself Sql in 10 Minutes by Ben Forta -
SQLServer 7.0 Books Online available for download from the following
Microsoft Web site:
Allaire also maintain an extensive knowledge basis and
tech support forums on their website.
CPCUG and TeraTech ColdFusion
TeraTech maintains a ColdFusion code cuttings called
ColdCuts at http://www.teratech.com/ColdCuts/.
This page also has links to about a dozen ColdFusion white papers in the CF
The Maryland ColdFusion User Group meets the second
Tuesday of each month at Backstreets Cafe, 12352 Wilkins Avenue, Rockville. See http://www.cfug-md.org/ for details and
The DC ColdFusion User Group meets the first Wednesday
each month at Figleaf , 16th and P St NW, Washington DC. See the
DCCFUG page on http://www.figleaf.com/
for details and directions.
In this article we have looked at how to search a database using ColdFusion and SQL SELECT and how to display the results in a table. We also looked at dynamic queries and ColdFusion variable types that you can use in your pages.
Michael Smith is president of TeraTech, a ten year old
Rockville Maryland based consulting company that specializes in ColdFusion,
Database and Visual Basic development. You can reach Michael at [email protected] or 301-424-3903.