Get Started with ColdFusion

Lesson 2: Using ColdFusion to Display Database Contents

Perhaps the biggest feature of ColdFusion is the amazingly simple way it interacts with databases. CFMX can tie into just about any database you can think of… Oracle, MS SQLServer, MySQL (my personal fave), MS Access, FileMaker, Sybase, Informix (my least favorite!) and more!

To work with CFMX and databases, you need to understand two things. ODBC and SQL.

• ODBC stands for Open Database Connectivity, and is the basis on which ColdFusion connects to most standard databases.
• SQL or “See-Quill” is the basic language that’s used to talk to databases. It’s a simple language that can make or break you as a developer. I suggest picking up a copy of Learn SQL in 10 Minutes by Ben Forta. This lesson assumes that you don’t know SQL, though, so I’ll keep it simple.

First things first: we need a database! To save time, I’ve created a very simple database for you to download and use. It requires at least Microsoft Access 2000 -- so make sure you have it before you continue.

Now, we need to create a simple ODBC connection. This will require connecting to CFMX Administrator, the control panel that allows you to interact with the ColdFusion Application Server.

1. Browse to 127.0.0.1:8500/cfide/administrator

2. Enter your password. You should know this… if you installed the server properly. If you forget it, look at CFFAQ.com for details.

3. In the left navigation, click the “Data Sources” link.

4. In the Data Source Name field, enter “CFData” without the quotes.

5. Open the dropdown list, select Microsoft Access and click ADD.

6. Browse the server to the database, or type in the path to the file manually.

7. Click SUBMIT.

8. If you did everything right, CFMX will display a message saying that the Data source was updated successfully, and you’ll see “OK” next to your source in the list of connected data sources.

Now that you have an ODBC connection, let’s look at the database itself. This database is about as simple as possible, and contains just one table that holds a few records. In the real world, this rarely happens.

If you’re unfamiliar with relational databases, Access is a good place to practice, but, a word to the wise: Access isn’t the best solution to your database needs. If you are looking at low traffic and minor data updates, then you’ll be fine with Access. If you are building a huge database against hundreds of thousands of records and/or expect high usage, consider trying MySQL… and if you can afford them trying SQLServer or Oracle.

Our database in this case contains a few records in the table named INFO. If you don’t know what a database table is, think of it as a big spreadsheet that holds all your data. Inside this table are records comprised of data stored in columns. It’s really quite simple.

This database contains the following columns:

 NAME  
 ADDRESS  
 CITY  
 STATE  
 ZIP  
 PHONE  
 EMAIL  
 NOTES  
 ID

Note that the ID column is your PRIMARY KEY. This is the standard way Access creates a unique identifier for each record in a database.

Now, we’re going to use CFML to suck some of this data out onto the screen.

1. Create a new folder under the CFMX Webroot, named LESSON2.

2. In your text editor, create a new file and save it as ALLDATA.CFM

3. Now, in that file, add this code between the BODY tags:

   <CFQUERY DATASOURCE=”CFdata” NAME=”getall”>  
SELECT *  
FROM Info  
ORDER BY Name ASC  
</CFQUERY>

4. Then below this code, add the following HTML:

   <table align=”center” border=”1”>  
<CFOUTPUT QUERY=”getall”>  
<tr>  
 <td>#CURRENTROW#</td>  
 <td>#NAME#</td>  
 <td>#ADDRESS#<BR>#CITY# #STATE#, #ZIP#</td>  
 <td>#PHONE#</td>  
 <td>#EMAIL#</td>  
 <td>#NOTES#</td>  
</tr>  
</CFOUTPUT>  
</table>

5. Save the page and view it in your browser.

It’s not pretty yet -- but it works!

Let’s review what this code does.

The CFQUERY tag is the means by which CFML retrieves data from a database. The attributes we specified, DATASOURCE and NAME, are required by CFMX. The NAME attribute of this tag is specified by you, and can be any word that’s easy to remember. Query names cannot contain spaces or punctuation.

Between the opening and closing CFQUERY tag is the SQL Query itself. This is not CFML, this is SQL, the database language. So just what did we tell the database to do? Let’s look at this in detail.

• SELECT * This tells the database to get records. The asterisk means all records. So In short, we’ve told the database to SELECT ALL.
• FROM Info Well, now that the database has been told to get all records, it follows that we need to tell it where to get them from. This line tells the database the name of the TABLE from which we want to select the data. In this case, we want the data from our table, Info.
• ORDER BY Name ASC This last line is really just gravy. It tells the database that, now that we’ve selected all data from the Info table, we want it organized by the column NAME in ascending alphabetical order. If you wanted to sort the data by descending order, you’d use the abbreviation DESC.

What’s so great about CFML and SQL together, is that you can easily use CFML to augment your query to retrieve exactly the data you need… which brings us to the last part of this lesson.