Like most developers who have been at it for a while, I've begun laboring under the illusion that I'm pretty good. I'd like to think that I've built some great solutions and solved some great problems in the past. However, I can assure you that nothing, NOTHING, gave me the rush and sense of accomplishment I got about a decade ago when I made my first server-side DB connection and displayed the data on a web page. I had been working with websites as well databases for quite some time and I though the recent merger of the two via server-side scripting languages such as ASP, ColdFusion, PHP, and Perl was the sexiest thing around. (Well, maybe not THE sexiest ... but close). Anyway, enough about my misguided youth. Let's get down to brass tax. This Tutorial assumes you have some basic understanding of HTML as well as database tables and query syntax. If not, you may struggle with some of the code.

We're going to cover 2 examples. The first will involve pulling data from 1 table and displaying it. The 2nd example will tie 2 tables together, apply some filter criteria, and display the data in grouped format.

Example #1

Let's assume you have an Employee database. It houses information about company departments, human resources, and (strangely enough) there's also an Employee table in there that contains personal data on each employee. Your boss has asked for a web based report showing the First Name, Last Name, and Date of Birth for all employees. You don't flinch cuz you know MAXO's got your back.

DSN's

The first thing you need to do is ensure that the DSN (Data Source Name) for your Employee database is created on your ColdFusion Server.

1) Open the ColdFusion Administrator.

2) Under Data Sources, click OLE DB.
The OLE DB Data Sources page displays any existing OLE DB Data Source Names that are available to ColdFusion:

3) Enter a name for the new data source and select an OLE DB Provider from the drop-down list.
Note: Do not name a ColdFusion data source "Registry" or "Cookie", as these words are reserved for use by ColdFusion.

4) Click Add. The Create OLE DB Interface Data Source page displays:

Note: This example shows a connection to an Access database. You would follow the same steps for connecting with other DB's as well. For more details on this visit Macromedia Live Docs.

Note: Sometimes in a hosted environment or inside a company with a less than responsive IT department, you may not have admin rights to the CF Administrative menu. In these situations you can create a DNS-Less connection to your DB. See MAXO's tutorial on DSN-Less connections for more details.

Querying The Database

O.K. So now that our server recognizes and has a connection to our database we can get down to the fun part. Let's assume that the name of your Employee table is "tblEmployees". It has many columns but the 3 that you are interested in are: First_Name, Last_Name, and DOB. You'll need to write a query that will pull retrieve this information for you. The query syntax will be as follows:

O.K. Let's break this down and see what we've gotten oursleves into.

The <cfquery tags tells the server that we're starting a query

The "Name" attribute of the query is important since we'll use it later to display the data.

The "Datasource" attribute should look familiar. It is the DSN title we created earlier. It is also critical since it tells the server which Database to query and contains all the connectivity info we applied to it earlier. This is what I like about DSN's. You create them once and you can use them over and over again. If your DB changes you update the DSN and you won't need to update all your code again.

I won't delve into the actual query syntax. If you wish to learn more about this, visit W3 Schools. It's a great resouce.

Displaying The Data

Now that we have successfully queried our database, let's look at how we would display the data.

As you can see, a majority of our code here is static HTML. We're using basic HTML to start a table and display the first row of column titles. In fact the only coldfusion tag here is the <cfoutput tag. It has a "query" attribute which refers to the name of the query we created in the previous section. This tells the server that we're not only getting ready to display ColdFusion variables, but we're also going to be displaying query data. When you specify a "query" attribute for a <cfoutput tag you're basically running a loop of the query results. Everything between start and close of the <cfoutput tag will take place once for each record in the recordset returned by the query. So if you, for example, had 5 employees in your table, the results might look something like this:

First Name	Last Name	Birthday
Jim	Thompson	3/5/1972
Joe	Shmoe	2/11/1965
Dubyah	Bush	4/13/666
Britney	Smears	3/17/2002
Tim	Duncan	8/9/1974

Example #2

Naturally, after the creating the report above, you're the toast of the town. You're a total rock star. You got a promotion, your boss loves you, you're golfing with the board of directors, and you've got groupies from the accounting department e-mailing you pictures of their calculators (not sure why). In short, life is grand. The company is booming thank to your report and now you've got dozens more employees. So now the old man is asking for more. He likes the original report but now he would like the following updates:

Include each employees department.

Group the results by department.

Only show the results for employees in California and Washington.

Your first inclination might be to run for cover. But like I told you earlier ... don't fret ... MAXO's got your back.

Querying The Database

Just like the previous example, the first thing we need to do is query the database. Let's assume that in addition to the Employee table tblEmployees we've also got a Department table, tblDepartments. This table contains Department data and for our purposes we're interested in the Department_ID and Department_Name fields. We also now need to consider the State and Department_ID fields in tblEmployees. The State field is a filter criteria and the Department_ID is the foreign key that ties the Employee table to the Department table. Considering all this, our query would look as follows:

<cfquery name="DepartmentData" datasource="MAXO">
     SELECT E.First_Name, E.Last_Name, E.DOB, D.Department_Name
     FROM tblEmployees E, tblDepartment D
     WHERE E.Department_ID = D.Department_ID
       AND (E.State = 'CA' OR E.State = 'WA')
     ORDER BY D.Department_Name, E.Last_Name
</cfquery>

Notice that this time we're querying 2 tables and joining them via the Department_ID field. We're also using table Aliases ("E" and "D") to save some typing. Also, we're using the State field from the Employee table as filter criteria to limit our results. Finally, we're sorting the data by Department Name and Employee Last Name.

Displaying the results

O.K. So now we've got the data we need but there's still one trick left. We can't just spew it all out in a table like we did in the previous example. Here, per the boss' request, we need to GROUP the data by Department. No problem. Lucky for us the <cfoutput> tag has a "Group" attribute which we can use as follows:

<table border="1">
     <tr bgcolor="#CCCCCC">
          <td><b>Department</b></td>
          <td>First Name</td>
          <td>Last Name</td>
          <td>Birthday</td>
     </tr>
     <cfoutput query="DepartmentData" Group="Department_Name">
          <tr bgcolor="##FFFFCC">
               <td colspan="4">#Department_Name#</td>
          </tr>
          <cfoutput>
               <tr>
                    <td></td>
                    <td>#First_Name#</td>
                    <td>#Last_Name#</td>
                    <td>#DOB#</td>
               </tr>
          </cfoutput>
     </cfoutput>
</table>

Notice that we have 2 <cfoutput> tags, one nested inside the other. The only time you can do this is if the outer tag has a "Group" attribute. In this case the data is grouped by Department_Name, meaning you only see the Department Name once and then all the records for that department are shown, then, on to the next department. The results would look something like this:

Department	First Name	Last Name	Birthday
Accounting
	Jim	Thompson	3/5/1972
	Joe	Shmoe	2/11/1965
Human Resources
	Dubyah	Bush	4/13/666
	Britney	Smears	3/17/2002
	Tim	Duncan	8/9/1974
IT
	Roy	Jones	2/2/1933
	Foster	Brown	8/14/1977
	Ben	Forta	7/11/1989

Tell me that not the sexiest thing you've every seen. :-)