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.