Accessibility

ColdFusion Article

 

Displaying database data on a web page


Table of Contents

  1. Introduction
  2. What is a relational database?
  3. Connecting to a database
  4. Accessing databases: SQL and the cfquery tag

Accessing databases: SQL and the cfquery tag

To enable access by different applications, databases use a standard command language called the Structured Query Language (SQL), which allows you to retrieve data from a database table through a SELECT statement that specifies the columns and the table:

SELECT Products.ProductName, Products.Description
    FROM Product

In order to let ColdFusion know that you would like an SQL statement passed to a database, use the cfquery tag:

<cfquery...>
   SQL statement
</cfquery>

ColdFusion knows only which database to send the query to when you add an attribute called datasource to the cfquery tag. ColdFusion uses this attribute to look up the connection information in the data source and sends the request to the appropriate database. In the following example, it requests product information from the Product table:

<cfquery DataSource="Orders"...>
     SELECT Product.ProductName, Product.Description
     FROM Product
   </cfquery>

If you want to query a database to retrieve information to display, you need to specify the Name attribute in the cfquery tag. Note that a SELECT statement returns data in the form of a result set. The Name attribute gives a name to the result set, so that you can output the query data:

<cfquery DataSource="Orders" Name="q_GetProducts">
     SELECT Product.ProductName, Product.Description
     FROM Product
   </cfquery>

Once the query has been set up, you can display the data to the web page.

Displaying or outputting query results

Now that the query has been executed and the result set named q_GetProducts is waiting in your server's memory, you can use the cfoutput tag to display the rows of data on your web page. The cfoutput tag is used to display data on web pages. You can use the cfoutput tag to display static text, but mostly you use it to display any dynamic content, such as variable values or query results:

<cfoutput>
  HTML, text, and dynamic content
</cfoutput>

To distinguish the HTML and static text from dynamic variables or query result sets, place pound signs (#) around each variable returned from a query that you wish to display. In this simple example, we set a variable called Name with a value of "Sue." To display the variable's value with the cfoutput tag, surround the variable with pound signs (#Name#) so that ColdFusion knows to resolve and thus output the variable.

The following code:

<cfset Name="Sue">
   
   <cfoutput>
     Hello, my name is #variables.Name#!
   </cfoutput>

displays the following:

Hello, my name is Sue!

For query recordsets, you output not just a single value but usually a result set with many rows to display. Therefore, in order to loop through all rows in the result set, you would use the Query attribute of the cfoutput tag. Inside the cfoutput tag you can then reference any of the columns returned to the result set, specifying the query name prefix instead of the database table names for the column values:

<cfoutput query="q_GetProducts">
      #q_GetProducts.ProductName#  #q_GetProducts.Price#<BR>
   </cfoutput>

This prints one line for each row in the q_GetProducts result set, as follows:

Baseball Wall Shelf 69.9900
Sports Ball Bean Bag 39.9900
Triple Play Table Lamp 79.9900

You can use the DollarFormat() function around the q_GetProducts.Price variable to display the variable in dollar format, as follows:

<cfoutput query="q_GetProducts">
      #q_GetProducts.ProductName#  #dollarformat(q_GetProducts.Price)#<BR>
   </cfoutput>

This prints one line for each row in the q_GetProducts result set, as follows:

Baseball Wall Shelf $69.99
Sports Ball Bean Bag $39.99
Triple Play Table Lamp $79.99

Notice that the prices are now formatted with a dollar sign and rounded to two decimal places. You can also use HTML tables to format the output:

<table width="500" border="1">
      <tr>
        <td><B>Product Name</B></td>
        <td><B>Product Price</B></td>
      </tr>
     <cfoutput query="q_GetProducts">
      <tr>
        <td>#q_GetProducts.ProductName#</td>
        <td>#dollarformat(q_GetProducts.Price)#<BR></td>
      </tr>
     </cfoutput> 
   </table>

This prints one table row for each row in the q_GetProducts result set, as follows:

Product Name Product Price
Baseball Wall Shelf $69.99
Sports Ball Bean Bag $39.99
Triple Play Table Lamp $79.99

In addition to the ways you can customize the display of your query data, you can use the following CFML tags to change other aspects of your data:

  • cfloop is a programmatic looping structure that allows you to loop over a list, an incremental number, or a query
  • cfgrid tag is a Java-based grid control that takes the data and displays it in a spreadsheet-like format

You can learn more about alternative ways to display cfoutput results in the Developing ColdFusion Applications manual, as well as in the CFML Reference manual.

Where to go from here

Using ColdFusion, you can query information from various data sources quickly and easily and display that information on a web page. First you make a connection to the database, whether it's an ODBC, JDBC, OLE-DB, or Native driver connection. Then you write CFML code to perform a cfquery tag, which executes SQL code against the database. In order to display the information, you use the cfoutput tag.

Other Resources

Page 4 of 4
Previous Page