Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Retrieving Data
Now that we have set up our data source connection, we can begin using that connection to pull information from our database and publish it in our web pages. Let's take a look at how this procedure works:
We create a ColdFusion template that contains instructions (in the form of ColdFusion tags) to retrieve data from the database.
When this template page is requested by a user, the ColdFusion Server software interprets these instructions.
ColdFusion Server then interrogates the database for the appropriate information.
The database responds with a recordset of data.
ColdFusion Server creates an HTML document containing the newly retrieved information and sends it back to the user.
This process is repeated every time that particular template is requested. This means that the user is always provided with up-to-date information from the database. Figure 5 illustrates the steps in this process.
)
Figure 5 Data request process.
Now that we have a connection to the data source set up with ColdFusion Administrator, we need a way to specify what information we want to retrieve from the database. This is done using something called Structured Query Language (SQL). SQL is an easy-to-understand language of simple keywords that can be used to manipulate data in a database. For example, you can use simple SQL instructions to retrieve all employees from a certain department or only those who were hired after a certain date.
In this step, we will concentrate on getting at data that is already in the database. In Step 6, "Updating, Inserting, and Deleting Database Records," we will look at using a web interface to modify existing information in our database.
NOTE
Although SQL is a standard language governed by the American National Standards Institute (ANSI) and the International Standards Organization (ISO), some database systems might use slightly different proprietary dialects. If you are having trouble with SQL statements, check the documentation for your particular database system.
You can find more information on SQL in the "Reference" section of the http://www.LearnColdFusionMX.com web site.
Basic SQL SELECT Statements
Before we can publish database information in our web pages, we have to select the records with which we want to work. This is done with, not surprisingly, a SQL SELECT statement. In its simplest form, we use a SELECT statement to specify the field names we want to use (separated by commas) and which table in the database contains those fields. The following is the syntax for a SELECT statement:
SELECT FieldName1, FieldName2[, etc] FROM TableName
For example, to retrieve the names of all of our employees, we would use the following SELECT statement:
SELECT FirstName, LastName FROM Employees
This would retrieve the information stored in the FirstName and LastName fields for all the records in the Employees table of our database.
The preceding SELECT statement returns the names in the order in which they are stored in the database. If we wanted to see those names listed alphabetically by last name, we could use the SQL keywords ORDER BY. The ORDER BY clause forces the information to be listed in alphabetical order (or numerical order, in the case of a number field) by the field specified.
For example, the following SQL statement would retrieve the FirstName and LastName fields of all records in the Employees table and would list them in alphabetical order by LastName:
SELECT FirstName, LastName FROM Employees ORDER BY LastName
By default, this statement will arrange the information in ascending order, A to Z. If you want to have the information listed in descending order, you would use the DESC (short for descending) keyword. To list your employees from Z to A, use the following SQL statement:
SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC
NOTE
The case (as in upper- or lowercase) of SQL statements usually does not matter. However, many developers put SQL commands in uppercase to make the statements more readable and to make it easier to distinguish SQL keywords from other information contained in the statement.
Using CFQUERY
By passing SQL statements to the database, we are essentially asking questions of the database. These can be questions like, "Who are all the employees in the company?" or "Who works in the sales department?" In database terms, these questions are called queries.
To get ColdFusion to pass a query to a database, we use the <CFQUERY> tag. The <CFQUERY> tag triggers ColdFusion Server to connect to a specified data source, pass a SQL statement, and retrieve the results.
The <CFQUERY> tag requires a closing tag and has two required attributes: DATASOURCE and Name. The Datasource attribute is the DSN of a data source connection that has been established using ColdFusion Administrator. The name attribute specifies a variable that will hold the results of the query when it is returned from the database. We can access the results of the query by calling that variable's name. The SQL statement for the query you want to execute goes between the opening <CFQUERY> tag and the closing </CFQUERY> tag. For example, to get ColdFusion Server to execute a query to our Staff data source, we could use the following block of code:
<CFQUERY NAME="qStaffList" DATASOURCE="Staff"> SELECT FirstName, LastName FROM Employees ORDER BY LastName </CFQUERY>
This code would instruct ColdFusion Server to use the data source connection specified with the DSN of Staff. Once connected, ColdFusion Server would pass the SQL statement to list all employees alphabetically by last name. When the database returned the appropriate information, it would be stored in a variable called qStaffList.
Table 1 describes several of the more commonly used attributes of the <CFQUERY> tag. For a complete list of attributes, see the "Reference" section of the http://www.LearnColdFusionMX.com web site.
Table 1 Commonly Used <CFQUERY> Attributes
|
Attribute |
Description |
Notes |
|
NAME (required) |
The name of the variable that will store the query results |
It must follow standard variable naming rules. |
|
DATASOURCE (required*) |
The name of the data source to run the query against |
This attribute is required unless you are using the ColdFusion Query of Queries feature. See the "Reference" section of the http://www.LearnColdFusionMX.com web site for more information. |
|
MAXROWS (optional) |
Specifies the number of records/rows that should be retrieved |
Use this attribute if you only want a certain number of records back, such as the top 10 values for a field. |
|
USERNAME (optional) |
The username for the database login |
Use if the database you are connecting to has security in place and requires a username and password to log in. |
|
PASSWORD (optional) |
The password for the database login |
Use if the database you are connecting to has security in place and requires a username and password to log in. |
Using <CFOUTPUT> to View Query Information
So far, we have only retrieved information with the <CFQUERY> tag. That information is currently stored as a Query scope variable whose name is specified in the <CFQUERY> tag. If we want to actually view this information, we must use the <CFOUTPUT> tag, just as we would with any other variable. When using <CFOUTPUT> with query information, however, there are few additional <CFOUTPUT> attributes to use. Table 2 lists the <CFOUTPUT> attributes associated with outputting query data.
Table 2 <CFOUTPUT> Attributes
|
Attribute |
Description |
Notes |
|
QUERY |
The name of the variable used to store the query results |
This is the name specified in the <CFQUERY> tag's NAME attribute |
|
MAXROWS |
The maximum number of rows you want to output |
If no MAXROWS attribute is specified, all rows will be displayed. |
|
STARTROW |
The first row you want to display |
Used commonly in search result sets, such as results 1–10, 11–20, 21–30, and so on. |
|
GROUP |
The column or field to group by |
Used to output all records from a group together. |
|
GROUPCASESENSITIVE |
Yes or No value |
Specifies whether the GROUP attribute should be treated as case sensitive. |
To display the information we retrieved with the <CFQUERY> previously listed, we would use the following <CFOUTPUT> block:
<CFOUTPUT QUERY="qStaffList"> #LastName#, #FirstName#<BR> </CFOUTPUT>
In this code, we use <CFOUTPUT> to display the fields we retrieved using the <CFQUERY> that we named qStaffList. Because we did not specify a MAXROWS attribute, this <CFOUTPUT> block will loop through all the results from our query. We use an HTML <BR> tag in the output to make sure that each employee ends up on a separate line in our web page; otherwise, all the employees will just be listed one after the other on the same line.
Notice that we do not have to output the fields in the same order they were retrieved. In our SQL SELECT statement, we asked for FirstName and then LastName. However, when we output that information, we output the LastName field followed by FirstName. After information has been retrieved using <CFQUERY>, you have great flexibility as to how that information is displayed.
Also notice that we did not specify the Query scope prefix for these variables, such as #qStaffList.LastName#. This is unnecessary because the scope of the variables has been specified by the QUERY attribute of the <CFOUTPUT> tag.
When using the <CFOUTPUT> tag with the QUERY attribute, three additional variables are created. These variables are listed in Table 3.
Table 3 Built-In Query-Related Variables
|
Variable Name |
Description |
|
ColumnList |
Produces a list of all the columns/fields returned by a query. Column names are separated by a comma. |
|
CurrentRow |
As a <CFOUTPUT> block loops through records in a query result, this variable keeps track of the current record/row number. |
|
RecordCount |
This variable contains the total number of records/rows returned by a particular query. |
NOTE
If you use one of these special variables inside a <CFOUTPUT> block that uses the QUERY attribute, you do not have to specify a query name prefix for the variable. However, if you are using one of these variables in a <CFOUTPUT> block that does not contain the QUERY attribute, you must specify the name of the query as a variable prefix. For example, the code
<CFOUTPUT>#qStaffList.RecordCount#</CFOUTPUT>
would output the number of records returned by the qStaffList query. In this case, the number is 12.