This chapter is from the book
This chapter is from the book
This chapter is from the book
Project II: Retrieving Data
Now that you have your database set up, it's time to pull some data from those tables. Here are a few basic elements of ColdFusion you need to know in order to make this happen. These will help you understand how ColdFusion works and give you some hands-on experience right up front.
<CFSET>
The <CFSET> tag allows you to set a value to a ColdFusion variable. For example:
Now that you have set the variables firstName and lastName, you can reference them anywhere in the ColdFusion template and they will hold these values. You will learn other ways you can use this as you are introduced to more functions.
Concatenating Variables
You can also very easily concatenate variables by assigning a variable to equal the value of two or more variables:
Now the variable wholeName will be: Mike Fredrick
<CFOUTPUT>
|
<CFOUTPUT |
|
|
|
QUERY="name of the query" |
Optional |
|
|
GROUP="query column" |
Optional |
|
|
GROUPCASESENSITIVE="yes | no" |
Optional |
|
|
STARTROW="row of query to start from" |
Optional |
|
|
MAXROWS="maximum number of rows to return" |
Optional |
|
|
</CFOUTPUT > |
|
|
The <CFOUTPUT> tag will allow you to output ColdFusion content requests to your browser, such as the variables that were set above.
TABLE 1–4 <CFOUTPUT> Attributes
|
Attribute |
Description |
|
QUERY |
The name set in the CFQUERY in which you are querying the database from. |
|
GROUP |
Defines the column in the query to use when sets of records are grouped. |
|
GROUPCASESENSITIVE |
Defines whether to group by case sensitivity. Default value is set to YES. |
|
STARTROW |
Defines the row of the recordset to start the output from. |
|
MAXROWS |
Defines the number of rows from the recordset you want returned. |
#—The pound (#) sign
Whenever you are going to be displaying a variable to the browser you must enclose the variable within pound characters. This lets the ColdFusion server know at runtime that this is not just ordinary text but it is a variable that must be processed before being displayed to the user's browser.
For example:
<CFOUTPUT>Welcome back firstName lastName!</CFOUTPUT>
Will output to the screen:
Welcome back firstName lastName!
If you wrap pound (#) signs around the variables FirstName and LastName, then ColdFusion will know to process these since they are not just ordinary characters.
For example:
<CFOUTPUT> Welcome back #firstName# #lastName#!</CFOUTPUT>
Will output to the screen:
Welcome back Mike Fredrick!
Keeping these basics in mind, we can now grab data from the Customer and Products table from the Shelley Catalog we built earlier in this chapter.
<CFQUERY>
|
<CFQUERY |
|
|
|
NAME="name of query" |
Required |
|
|
DATASOURCE="datasource name" |
Required |
|
|
DBTYPE="database type" |
Optional |
|
|
DBSERVER="database server" |
Optional |
|
|
DBNAME="database name" |
Optional |
|
|
USERNAME="username" |
Optional |
|
|
PASSWORD="password" |
Optional |
|
|
MAXROWS="maximum number of rows" |
Optional |
|
|
BLOCKFACTOR="block-size" |
Optional |
|
|
TIMEOUT="time in milliseconds" |
Optional |
|
|
CACHEDAFTER="date" |
Optional |
|
|
CACHEDWITHIN="time-span" |
Optional |
|
|
PROVIDER="COM provider" |
Optional |
|
|
PROVIDERDSN="datasource name" |
Optional |
|
|
DEBUG="Yes | No" |
Optional |
|
|
</CFQUERY> |
|
|
TABLE 1–5 <CFQUERY> Attributes
|
Attribute |
Description |
|
NAME |
Defines what you would like to name the query. |
|
DATASOURCE |
Defines the name of the data source you set up in your ODBC connection. |
|
DBTYPE |
Defines the database driver type. By default, ODBC is set which is perfectly fine for most cases. If using other types of connections like DB2, Informix, OLEDB, Oracle, or Sybase use the following accordingly:
|
|
DBSERVER |
Used with native drivers to specify the database server machine. |
|
DBNAME |
SQLOLEDB and Sybase drivers specific; specifies the name of the database. |
|
USERNAME |
Defines the username set on the data source. |
|
PASSWORD |
Defines the password set on the data source. |
|
MAXROWS |
Defines the maximum number of rows to return from the recordset. |
|
BLOCKFACTOR |
Used to set a maximum number of records an Oracle or ODBC driver will return from a query. |
|
TIMEOUT |
Defines the time in milliseconds to timeout from query. |
|
CACHEDAFTER |
Returns cached data from the same query that was executed previously. SQL statement and data source must be the same as the original to retrieve cached data. |
|
CACHEDWITHIN |
Retrieves the cached data if the query was run within the parameter supplied here. Use the ColdFusion function CreateTimeSpan to create a valid entry. |
|
PROVIDER |
Defines the COM provider and is used for OLE-DB only. |
|
PROVIDERDSN |
Defines the name of the COM provider and is used for OLE-DB only. |
|
DEBUG |
Used for debugging queries. |
The <CFQUERY> tag is the method of communication to the ColdFusion application server, which allows you to retrieve data from a data source. <CFQUERY> uses SQL to extract the data you are looking for. For example, let's start the application with a statement to retrieve all the customers.
<CFQUERY NAME="getCustomers" DATASOURCE="shelleyCatalog">
SELECT *
FROM Customer
</CFQUERY>
How this works
NAME defines the name used to reference this query statement. DATASOURCE is the name of the data source you set up in your ODBC connection. In this case we are using shelleyCatalog.
Using the SQL statement SELECT you will select ALL the fields using the * wildcard character.
Using the SQL statement FROM you will SELECT * FROM the Customer table.
Close the <CFQUERY> statement with </CFQUERY>.
Now that you have made your request to the database for the information, you can display it to the browser with <CFOUTPUT>.
The <CFOUTPUT> now has the QUERY attribute attached to it and the name of the query that you specified with <CFQUERY> as the NAME attribute. <CFOUTPUT> will now loop through all the records pulled from the <CFQUERY> and display them. We will throw this into a table to produce a nicely formatted output.
Script 1-1 firstQuery.cfm
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>My First QUERY!</TITLE>
</HEAD>
<BODY>
<CFQUERY NAME="getCustomers" DATASOURCE="shelleyCatalog">
SELECT *
FROM Customer
</CFQUERY>
<H2>Customers of Shelley Biotechnologies, Inc.</H2>
<TABLE BORDER="0">
<CFOUTPUT QUERY="getCustomers">
<TR>
<TD>#customerID#</TD>
<TD>#firstName#</TD>
<TD>#lastName#</TD>
<TD>#address#</TD>
<TD>#city#</TD>
<TD>#state#</TD>
<TD>#zip#</TD>
<TD>#country#</TD>
<TD>#phone#</TD>
<TD>#email#</TD>
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>
FIGURE 1–13 Output of all the fields in the Customer table.
){kind=link}