Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

For this example, suppose that you are a developer for a credit card company that wants to Web-enable many of its customer service functions. The function you are tasked with coding is the change of address. As part of this function, you have to code the following form to collect the new address information:

<FORM ACTION=“updateaddr.cfm” METHOD=“POST”>
Please enter your credit card number:
<INPUT TYPE=“TEXT” NAME=“card_number”>
<P>
Please enter your new address below:
<P>
Street:
<INPUT TYPE=“TEXT” NAME=“new_street” SIZE=20>
<P>
City:
<INPUT TYPE=“TEXT” NAME=“new_city” SIZE=20>
<P>
State:
<INPUT TYPE=“TEXT” NAME=“new_state” SIZE=20>
<P>
Zip:
<INPUT TYPE=“TEXT” NAME=“new_zip” SIZE=20>
<P>
<INPUT TYPE=“SUBMIT” VALUE=“Update my Address”>
</FORM>

In addition to the new address information, you also have to ask for something to uniquely identify the customer. In this case, you can use the credit card number itself because you know that each customer has a unique number.

Now you need to write the template updateaddr.cfm to do the update operation and display a confirmation message so that customers know that their information was changed. To do the update, you would again use the <CFQUERY> tag:

<CFQUERY DATASOURCE=“customers” NAME=“UpdateAddr”>
UPDATE Contact_Info
SET street=’#Form.new_street#’,city=‘#Form.new_city#’,
   state=‘#Form.new_state#’,zip=‘#Form.new_zip#’
WHERE customer_ID = ‘#Form.card_number#’
</CFQUERY>

The SQL UPDATE command is used to do the update, and you can see how each field is being set equal to the new values submitted on the form. What’s very important in the SQL statement is the WHERE clause, which restricts the update to the record belonging to the user with the credit card number entered on the form. Without the WHERE clause, the street, city, state, and zip columns of every record in the database would have been updated to reflect the new address information! The moral of the story is this: When doing updates, make sure that you are keying in on the record or records that require the updates by using the WHERE clause in your SQL statement. Otherwise, your updates will be made globally—usually with disastrous results.

---

Unique Record Identifiers

In relational database theory, the column that uniquely identifies a record in a table is called the table’s primary key. The table is indexed by the primary key values, so searching by the primary key value is typically rapid and efficient. In the credit card example above, you could conceivably use the customer’s credit card number as a primary key, but primary keys are usually shorter than a 16-digit credit card number. In fact, primary keys are usually numeric fields that are automatically incremented each time a new record is inserted into the database. This ensures that each primary key is unique.

Tables in a well-designed relational database usually contain pointers to records in other tables. You might have a table, for example, where you store all transactions posted for an electronic commerce site. You know that a customer is associated with that transaction, but rather than put the customer’s name and address into the transaction table, you can include the primary key of the customer’s entry in your contact information table. That way, if you need that customer’s contact information, you can use the primary key for the contact information table to do a quick query into that table and retrieve the information. When you use a primary key from one table as a pointer in a second table, you are said to be putting a foreign key into the second table.

---

Listing 34.3 shows a complete listing of one possible way to code the template updateaddr.cfm so that it does the update and notifies the user about the change.

Listing 34.4 Automatic Deletion of Old Comments

<CFQUERY DATASOURCE=“feedback” NAME=“GetDeleteCandidates”>
SELECT visitor,comment
FROM Comments
WHERE entry_date < #CreateODBCDate(Now() - CreateTimeSpan(30,0,0,0))#
</CFQUERY>

<HTML>

<HEAD>
<TITLE>Deleted Comments</TITLE>
</HEAD>

<BODY BGCOLOR=“WHITE”>

<B>The following comments have been deleted:</B>
<P>

<TABLE>
<TR><TH>Visitor Name</TH><TH>Comment</TH></TR>
<CFOUTPUT QUERY=“GetDeleteCandidates”>
<TR><TD>#visitor#</TD><TD>#comment#</TD></TR>
</CFOUTPUT>
</TABLE>

</BODY>

</HTML>

<CFQUERY DATASOURCE=“feedback” NAME=“PurgeOldComments”>
DELETE
FROM Comments
WHERE entry_date < #CreateODBCDate(Now() - CreateTimeSpan(30,0,0,0))#
</CFQUERY>

---

NOTE:  The delete query in Listing 34.4 could have occurred before the HTML output section. After you have the delete candidate information stored in the query named GetDeleteCandidates, it doesn’t matter where in the template you do the deletion.

---

Using Decision Statements

Conditional logic is another basic component of any programming language, and ColdFusion enables you to build conditional processing into your applications with the <CFIF> tag. Instead of taking a particular attribute, the <CFIF> tag has to contain one of ColdFusion’s eight decision operators:

•  IS

•  IS NOT

•  GREATER THAN

•  LESS THAN

•  GREATER THAN OR EQUAL TO

•  LESS THAN OR EQUAL TO

•  CONTAINS

•  DOES NOT CONTAIN

Each of these operators is evaluated to a Boolean TRUE or FALSE value. If the operator evaluates to TRUE, the code between the <CFIF> and </CFIF> tags is executed. Otherwise, the code is ignored.

	Many of the decision operators can be abbreviated to a more compact form. For example, you can abbreviate LESS THAN to LT and GREATER THAN OR EQUAL TO to GE.