ColdFusion Tutorial Part III

Introduction:

Welcome to the final chapter of this article. If you have come this far you should have a firm grasp of how to use ColdFusion to make a dynamic site. In this part I will cover code you would find inside the <CFQUERY> tags, and more advanced SQL.

1. Adding Data

To add data to your database you will use the INSERT SQL statement.

Figure 1. The INSERT Statement

INSERT INTO Friends(Name, Sex, FavMovie)

VALUES (Chris, Male, Braveheart)

How it works is you specify the table you wish to insert into, in our case "Friends", and then the columns in parenthesis. On the next line you specify the values in much the same way. The order the values are listed must match with the order the columns are listed. You can also make this insert statement dynamic.

Figure 2. A Dynamic INSERT Statement

INSERT INTO Friends(Name, Sex, FavMovie)

VALUES (#name#, #sex#, #movie#)

As was shown in the first part of this article, any aspect of the SQL Statement can be replaced with form input, this holds for the INSERT statement. Most database driven websites have an admin section using dynamic statements like this to add new content.

2. Changing Data

To modify existing data you must use the UPDATE statement.

Figure 3. The UPDATE Statement

UPDATE Friends

SET Age = '21'

WHERE Name like "Chris"

How this works is you first specify the table which you want to update, in our case "Friends", Then you pick the value using the SET keyword. If you wanted to update multiple values you would separate each by a comma, for instance SET Age = ’21’, FavMovie = ‘The Patriot’. Finally we include the where clause, without it all rows would be updated. The UPDATE statement is very useful, for instance in our friends database we would use it to change someone’s age when they have a birthday.

3. Deleting Data

You should always be very careful when deleting data. A slip of the mind or a typo and you could have an empty database.

Figure 4. The DELETE Statement

DELETE FROM Friends

WHERE Name = "Tim"

Lets say you had a falling out with a friend. You could delete their name from your friends database by using the above statement. Again the first thing specified is the table name followed by an optional WHERE statement. However remember that though the where statement is optional, without it everything in the table would be erased.

4. The Distinct Keyword

Sometimes when working with a database you do not want to return duplicate values. For instance when working with a Used Car database I wanted to dynamically generate a select form for vehicle make on the website. If I hadn’t used the distinct keyword the select list would have been very long, it would have had 1 option for every car in the database. Using the distinct keyword in my SELECT statement limited the output to every distinct make within the database.

Figure 5. The DISTINCT Keyword

Select DISTINCT Make

FROM Used

As you can see it looks just like a regular SELECT statement, just with the DISTINCT keyword thrown in to eliminate duplicate values.

5. Column Aliases

There may come a time when you wish to use aliases when referring to your columns. An alias is just a name you assign to a column to make it easier to refer to, it in no way changes your database, it only changes how the CF interpreter sees the column.

Figure 6. Aliases

<CFQUERY DATASOURCE = "Friends" name = "Query1">

Select DISTINCT FavMovie as Movie

From Friends

</CFQUERY>

.

.

.

<CFOUTPUT QUERY = "Query1">

#Movie#

</CFOUTPUT>

What the above would do is return a distinct list of your friends favorite movies. The only difference it has with a normal select statement is that within the page we can refer to the FavMovie column as Movie since we established the alias.

6. Aggregate Functions

Aggregate Functions are used to summarise the results from your queries. The most common use for this is to display the number of results returned from a search. To do this you must use the COUNT function.

Figure 7. Count Function

Select COUNT(*) as Friends

From Friends 

The above will return the number of friends you have. Notice the asterisk in the SQL, this is used because we want to simply count the total number of records. The asterisk can be replaced with a column name to just count the number of records in a particular column.

There are more aggregate functions of course, they are all used in the same way, and are listed below:

Count() – Counts the number of results.
Sum() – Calculates the total of values returned.
Avg() – Calculates the average of values returned.
Min() – Calculates the smallest value, earliest date, or first entry alphabetically.
Max() – Calculates the largest value, latest date, or last entry alphabetically.

7. Sorting Results

When you run a select statement the results will be returned in the order they were entered into the table. This isn’t always what you want, sometimes you may want to order the results by a set of criteria. To do this you use the Order by clause.

Figure 8. Ordering Results

Select Firstname, Lastname

From Friends

Order by Lastname

What the above will do is return a list of your friends name’s ordered by their last name alphabetically. If you wanted to start with the Z’s and work your way to the A’s you would change it to read Order by Lastname DESC. DESC is short for descending and will reverse the order in which the records are listed.

But what if you want to order by more than one column? Lets assume you have a friend named John Smith and a friend named Peter Smith. They will be listed in the right place by their last name but whether or not John is above Peter is dependent on the order you entered them into the database.

To fix this you simply pass two column names in the Order clause.

Figure 9. Ordering by Two Columns

Select Firstname, Lastname

From Friends

Order by Lastname, Firstname

8. Limiting Results

Often times it is desirable to only display 10 records at a time, for instance most search engines work this way. To do this you add a few arguments to your query tag.

Figure 10. Limiting Results

<CFQUERY DATASOURCE = "Friends" Name = "Query1" Maxrows = "10" Startrow = "0">

..

..

..

</CFQUERY>

Note that the starting row is set at 0. Remember that computers start numbering at 0, not 1. So the first record in the database is record number 0.

Conclusion:

If you’ve made it this far then you should be able to make a database driven website using ColdFusion quite easily. I have covered what is needed to achieve the most commonly desired results of a database driven website, but I have only scratched the surface. There is a lot more to ColdFusion and relational databases that I couldn’t cover in a short tutorial. If you would like to learn more I suggest ColdFusion Web Application Construction Kit 4.0 by Ben Forta. It is without a doubt the most comprehensive book I own and it should be able to assist you in all your ColdFusion endeavors.