The Cold Fusion Web Database Construction Kit
Chapter 11, "Cold Fusion Forms," introduced the HTML FORM tags. Using the Cold Fusion data collection capabilities, you learned how to create powerful search front ends.
Cold Fusion forms are used wherever data collection is required. In this chapter, you learn how to use forms to add new data to tables and how to update existing data.
Cold Fusion provides special data manipulation tags to simplify the process of adding and updating data. More often than not, these tags will be all you need to provide your users with web based data manipulation. More complex data manipulation and deleting data will require the manual creation of SQL statements.
Your online employee list was well received. Everyone now has access to up-to-date employee lists, and they can search for employees by name, department, or phone extension.
You, and your users, discover that a web browser can be used as a front end to access almost any type of data. Using the same front end, a web browser, makes it easier for people to switch between applications and greatly lowers the learning curve that each new application introduces. Why? Because there is only one application to learn--the Web browser itself.
The popular term to describe this type of front end application is universal client. This means that the same client application, your web browser, is used as a front end to multiple applications.
When we created the employee search forms in Chapter 11, "Cold Fusion Forms," we had to create two templates for each search. One created the user search screen containing the search form, and the other performed the actual search using the Cold Fusion CFQUERY tag.
Breaking an operation into more than one template is typical of Cold Fusion and indeed, all Web-based data interaction. As explained in Chapter 2, "Introduction to Cold Fusion," a browser's connection to a web server is made and broken as needed. Whenever a Web page retrieves, a HTTP connection is made to a web server. That connection is broken as soon as that page retrieves. Any subsequent pages retrieve with a new connection that is used only to retrieve that page.
There is no way to keep a connection alive for the duration of a complete process; for example, searching for data. Therefore, the process must be broken up into steps, and each step is a separate template.
Adding data via your web browser is no different. You'll need at least two templates to perform the insertion. One displays the form that you'll use to collect the data, and the other processes the data and inserts the record.
Adding data to a table involves the following steps:
Forms used to add data are no different from the forms we created to search for data. The form creates using the standard HTML FORM and INPUT tags, as shown in Listing 12.1.
<HTML> <HEAD> <TITLE>Add an Employee</TITLE> </HEAD> <BODY> <H1>Add an Employee</H1> <FORM ACTION="empadd2.cfm" METHOD="POST"> <P> First name: <INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30"> <BR> Last name: <INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30"> <BR> Phone Extension: <INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4"> <P> <INPUT TYPE="submit" VALUE="Add Employee"> <INPUT TYPE="reset" VALUE="Clear"> </FORM> </BODY> </HTML>
The FORM ACTION attribute specifies the name of the template to use to process the insertion; in this case, EMPADD2.CFM.
Each INPUT field has a field name specified in the NAME attribute. These names correspond to the names of the appropriate columns in the Employees table.
We also specified the SIZE and MAXLENGTH attributes in each of the text fields. SIZE specifies the size of the text box within the browser window. Without the SIZE attribute, the browser will use its default size. This size varies from one browser to the next.
The SIZE attribute does not restrict the number of characters that may be entered into the field. SIZE="30" creates a text field that occupies the space of 30 characters, but if you enter more than 30 characters, the text scrolls within the field. To restrict the number of characters that may be entered, you must use the MAXLENGTH attribute. MAXLENGTH="30" instructs the browser to insert 30 or less characters in the field.
The SIZE attribute is primarily used for aesthetics and the control of screen appearance. MAXLENGTH ensures that only data that can be handled is entered into a field. Without MAXLENGTH, users could enter more data than would fit in a field, and that data would be truncated upon insertion.
You do not have to specify the same SIZE and MAXLENGTH values. The following example only allocates 20 characters of screen space for the field but allows 30 characters to be entered. Once 20 characters have been entered into the field, the text scrolls to accommodate the extra characters.
<INPUT TYPE="text" NAME="FirstName" SIZE="20" MAXLENGTH="30">
TIP: For maximum control over form appearance and data entry, you should always use both the SIZE and MAXLENGTH attributes. Without these, the browser uses its own defaults, and there is no way to determine what these defaults are.
The add employee form is shown in Figure 12.1.
Figure 12.1 HTML forms can be used as a front end for data insertion.
Now you need a template to process the actual data insertion. To add the row, we'll use the SQL INSERT statement.
See the section in Chapter 9, "SQL Data Manipulation,"entitled "Adding Data," for a detailed discussion of the SQL INSERT statement.
As shown in Listing 12.2, the CFQUERY tag can be used to pass any SQL statement, not just SELECT statements. The SQL statement here is an INSERT statement and adds a row to the Employees table and sets the FirstName, LastName, and PhoneExtension columns to the form values passed by the browser.
){kind=link}
<CFQUERY
DATASOURCE="A2Z"
>
INSERT INTO Employees(FirstName, LastName, PhoneExtension)
VALUES(`#FirstName#', `#LastName#', `#PhoneExtension#')
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee Added</TITLE>
</HEAD>
<BODY>
<H1>Employee Added</H1>
<CFOUTPUT>
Employee <B>#FirstName# #LastName#</B> added.
</CFOUTPUT>
</BODY>
</HTML>
Save this template as C:\A2Z\SCRIPTS\EMPADD2.CFM, and execute the EMPADD1.CFM template with your browser. Try adding an employee to the table. Your browser display should look like the one shown in Figure 12.2.
Figure 12.2 Data can be added with Cold Fusion by using the SQL INSERT statement.
You can verify that the employee was added by browsing the table with Microsoft Access, Microsoft Query, or any of the employee search templates that we created in the last chapter.
The example in Listing 12.2 demonstrated how to add data to a table using the standard SQL INSERT command. This works very well if you only have to provide data for a few columns, and if those columns are always provided. If the number of columns can vary, using SQL INSERT gets rather complicated.
Suppose you had two or more data entry forms for similar data. One might collect a minimal number of fields, while another collected a more complete record. How would you create a SQL INSERT statement to handle both sets of data?
What you should try to avoid is creating two separate templates with a different SQL INSERT statement in each. As a rule, you should never have more than one template perform a given operation so you don't run the risk of future changes and revisions not being applied correctly. If a table name or column name changes, for example, you won't have to worry about forgetting one of the templates that references the changed column.
TIP: As a rule, you should never create more than one template to perform a specific operation. This helps prevent you from introducing errors into your templates in the future when updates or revisions are made. You should create one template with conditional code rather than creating two separate templates.
Another solution is to use dynamic SQL. The dynamic SQL techniques introduced in Chapter 11, "Cold Fusion Forms," enable you to gradually construct SQL statements, and you can create an INSERT statement specific to the fields that are passed. You would build the basic INSERT statement in the CFQUERY tag and then add columns with the CFSQL tag if that field was passed.
Although this might be a workable solution, it is not very efficient. The conditional SQL INSERT code is far more complex than conditional SQL SELECT. The INSERT statement requires that both the list of columns and the values be dynamic. Plus, the INSERT syntax requires that you separate all column names and values by commas. This means that every column name and value must be followed by a comma except the last one in the list. Your conditional SQL will have to accommodate these syntactical requirements when the statement is constructed.
A better solution is to use the CFINSERT tag. CFINSERT is a special Cold Fusion tag that hides the complexity of building dynamic SQL INSERT statements. CFINSERT takes the following parameters as attributes:
Look at the following Cold Fusion tag:
<CFINSERT DATASOURCE="A2Z" TABLENAME="Employees">
This code does exactly the same thing as the CFQUERY tag in Listing 12.2. When Cold Fusion processes a CFINSERT tag, it builds a dynamic SQL INSERT statement under the hood. If a FORMFIELDS attribute is provided, the specified field names are used. In our example, no FORMFIELDS attribute was specified, so Cold Fusion automatically uses the form fields that were submitted, building the list of columns and the values dynamically.
< CFINSERT DATASOURCE="A2Z" TABLENAME="Employees" > <HTML> <HEAD> <TITLE>Employee Added</TITLE> </HEAD> <BODY> <H1>Employee Added</H1> <CFOUTPUT> Employee <B>#FirstName# #LastName#</B> added. </CFOUTPUT> </BODY> </HTML>
Try modifying the form in template EMPADD1.CFM so that it submits the form to template EMPADD3.CFM instead of EMPADD2.CFM, and then add a record. You'll see the code in Listing 12.3 does exactly the same thing as the code in Listing 12.2, but with a much simpler syntax and interface.
Because CFINSERT builds its SQL statements dynamically, EMPADD3.CFM can be used even if you add fields to the data entry form. Listing 12.4 contains an updated template which adds several fields to the "Add an Employee" form. But even so, it still submits data to the same template that we just created.
<HTML> <HEAD> <TITLE>Add an Employee</TITLE> </HEAD> <BODY> <H1>Add an Employee</H1> <FORM ACTION="empadd3.cfm" METHOD="POST"> <P> First name: <INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30"> Middle Initial: <INPUT TYPE="text" NAME="MiddleInit" SIZE="1" MAXLENGTH="1"> <BR> Last name: <INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30"> <BR> Title: <INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="20"> <BR> Phone Extension: <INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4"> <BR> E-Mail: <INPUT TYPE="text" NAME="EMail" SIZE="30" MAXLENGTH="30"> <P> <INPUT TYPE="submit" VALUE="Add Employee"> <INPUT TYPE="reset" VALUE="Clear"> </FORM> </BODY> </HTML>
Try adding an employee using this new form, your browser should look like the one shown in Figure 12.3.
Figure 12.3 The Cold Fusion CFINSERT tag builds SQL INSERT statements dynamically based on the forms fields provided.
CFINSERT instructs Cold Fusion to build SQL INSERT statements dynamically. Cold Fusion automatically uses all submitted form fields when building this statement.
Sometimes you might want Cold Fusion not to include certain fields. For example, you might have hidden fields in your form that are not table columns, like the hidden field shown in Listing 12.5. That field might be there as part of a security system you have implemented, and it is not a column in the table. If you try to pass this field to CFINSERT, Cold Fusion passes the hidden Login field as a column. Obviously this generates an ODBC error, as seen in Figure 12.4.
<HTML> <HEAD> <TITLE>Add an Employee</TITLE> </HEAD> <BODY> <H1>Add an Employee</H1> <FORM ACTION="empadd3.cfm" METHOD="POST"> <INPUT TYPE="hidden" NAME="Login" VALUE="Bob" > <P> First name: <INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30"> Middle Initial: <INPUT TYPE="text" NAME="MiddleInit" SIZE="1" MAXLENGTH="1"> <BR> Last name: <INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30"> <BR> Title: <INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="20"> <BR> Phone Extension: <INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4"> <BR> E-Mail: <INPUT TYPE="text" NAME="EMail" SIZE="30" MAXLENGTH="30"> <P> <INPUT TYPE="submit" VALUE="Add Employee"> <INPUT TYPE="reset" VALUE="Clear"> </FORM> </BODY> </HTML>
Figure 12.4 An ODBC error message generates if Cold Fusion tries to INSERT fields that are not table columns.
To solve this problem, use the FORMFIELDS attribute. FORMFIELDS instructs Cold Fusion to only process form fields that are in the list. Any other fields are ignored.
It is important to note that FORMFIELDS is not used to specify which fields Cold Fusion should process. Rather, it specifies which fields should not be processed. The difference is subtle. Not all fields listed in the FORMFIELDS value need be present. If they are, they are processed, and if not listed, they won't be processed. Any fields that are not listed in the FORMFIELDS list are ignored.
Listing 12.6 contains an updated data insertion template. The CFINSERT tag now has a FORMFIELDS attribute, so Cold Fusion ignores the hidden Login field in EMPADD5.CFM. The code
FORMFIELDS="FirstName, MiddleInit, LastName, Title, PhoneExtension, EMail"
ensures that only these fields are processed, and any others are ignored.
< CFINSERT DATASOURCE="A2Z" TABLENAME="Employees" FORMFIELDS="FirstName, MiddleInit, LastName, Title, PhoneExtension, EMail" > <HTML> <HEAD> <TITLE>Employee Added</TITLE> </HEAD> <BODY> <H1>Employee Added</H1> <CFOUTPUT> Employee <B>#FirstName# #LastName#</B> added. </CFOUTPUT> </BODY> </HTML>
Another situation in which CFINSERT FORMFIELDS can be used is when a form collects data that needs to be added to more than one table. Using FORMFIELDS you can create a template that has two or more CFINSERT statements.
As long as each CFINSERT statement has a FORMFIELDS attribute that specifies which fields are to be used with each INSERT, Cold Fusion correctly executes each CFINSERT with its appropriate fields.
Adding data to tables using the Cold Fusion CFINSERT tag is simpler and helps prevent the creation of similar, multiple templates.
Is there ever a reason to use SQL INSERT instead of CFINSERT?
Both are needed. CFINSERT can only be used for simple data insertion into a single table. If you wanted to INSERT the results of a SELECT statement, you could not use CFINSERT. Similarly, if you wanted to INSERT values other than FORM fields, perhaps variables, or URL para-meters, you would not be able to use CFINSERT.
Here's the rule of thumb:
Updating data with Cold Fusion is very similar to inserting data. To update a row, you need two templates, a data entry form template, and a data update template. The difference between a form used for data addition and one used for data modification is that the latter needs to be populated with existing values, like the screen shown in Fig- ure 12.5.
Figure 12.5 When using forms to update data, the form fields usually need to be populated with existing values.
Populating an HTML form is a very simple process. You first need to retrieve the row to be updated from the table. You'll do this with a standard CFQUERY. The retrieved values are then passed as attributes to the HTML form.
See the section in Chapter 10, "Cold Fusion Basics," entitled "The CFQUERY Tag," for a detailed discussion of the Cold Fusion CFQUERY tag and how it is used.
Listing 12.7 contains the code for EMPUPD1.CFM, a template to update an employee record. To test this template, you must specify an employee ID, because without it, Cold Fusion would not know what row to retrieve. To ensure that an employee ID is passed, check for the existence of the EmployeeID parameter. The code
<CFIF #ParameterExists(EmployeeID)# IS "No">
will return TRUE only if EmployeeID was not passed, in which case an error message is sent back to the user and template processing is halted with the <CFABORT> tag.
Without the <CFABORT> tag, Cold Fusion continues processing the template. When the CFQUERY statement is processed, an error message generates because WHERE EmployeeID = #EmployeeID# (the WHERE clause) would be referencing an inexistent field.
Test the EMPUPD1.CFM template, passing &EmployeeID=7 as a URL parameter. Your screen should look like the one shown in Figure 12.5 above.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<CFIF #ParameterExists(EmployeeID)# IS "No">
Error! No EmployeeID was specified!
<CFABORT>
</CFIF>
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employee"
>
SELECT FirstName,
MiddleInit,
LastName,
Title,
PhoneExtension,
EMail
FROM Employees
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
<CFOUTPUT QUERY="Employee">
<HTML>
<HEAD>
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>
</HEAD>
<BODY>
<H1> Update an Employee - #LastName#, #FirstName#</H1>
<FORM ACTION="empupd2.cfm" METHOD="POST">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
<P>
First name:
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30" VALUE="#Trim(FirstName)#">
Middle Initial:
<INPUT TYPE="text" NAME="MiddleInit" SIZE="1" MAXLENGTH="1" VALUE="#Trim(MiddleInit)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30" VALUE="#Trim(LastName)#">
<BR>
Title:
<INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="20" VALUE="#Trim(Title)#">
<BR>
Phone Extension:
<INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4" VALUE="#Trim(PhoneExtension)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="EMail" SIZE="30" MAXLENGTH="30" VALUE="#Trim(EMail)#">
<P>
<INPUT TYPE="submit" VALUE="Update Employee">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
</CFOUTPUT>
Before we create the data update template, let's take a closer look at Listing 12.7. The template is similar to the Add an Employee template, but has some important differences.
Cold Fusion first retrieves the employee data with the CFQUERY tag. The WHERE clause WHERE EmployeeID = #EmployeeID# selects data by the primary key value ensuring that no more than one row retrieves. The rest of the template is contained with a CFOUTPUT tag, enab-ling use of any of the retrieved columns within the page body.
See the section in Chapter 6, "Database Fundamentals," entitled "Primary and Foreign Keys," for a detailed discussion of table keys and how they uniquely identify every row in a table.
See the section in Chapter 10, "Cold Fusion Basics," entitled "Displaying Query Results with the CFOUTPUT Tag," for an explanation of the Cold Fusion CFOUTPUT tag.
The retrieved data is used throughout the template. Even the page title is dynamically created with the code
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>.
To populate the data entry fields, the current field value is passed to the INPUT VALUE attribute. For employee 7, Kim Black, the code is
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30" VALUE="#Trim(FirstName)#"> becomes <INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30" VALUE="Kim">,
When the FirstName field is displayed, the name "Kim" appears in it.
To ensure that there are no blank spaces after the retrieved value, the fields are trimmed with the Cold Fusion Trim() function before they are displayed. Why would you want to do this? Some databases, like Microsoft SQL Server, inserts spaces in text fields with spaces so that they take up the full column width in the table. The FirstName field is a 30 character wide column so the name "Kim" would be retrieved with 27 spaces after it. The extra space can be very annoying when you try to edit the field. For example, to append text to a field, you'd first have to backspace or delete all those extra characters.
TIP: When populating forms with table column values, it always is a good idea to trim the field first. Unlike spaces in the standard browser output, spaces in form fields are not ignored. Removing them allows easier editing. The Cold Fusion Trim() function removes spaces at the beginning and end of the value. If you only wanted to trim trailing spaces, you could use the RTrim() function instead.
See Appendix A, "Cold Fusion Reference," for a complete explanation of the Cold Fusion Trim() functions.
There is one hidden field in the FORM. The code
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
creates a hidden field called EmployeeID that contains the ID of the employee being updated. This hidden field must be present. Without it, Cold Fusion would have no idea what row you were updating when you submit the form.
Remember, HTTP sessions are created and broken as needed, and every session stands on its own. When Cold Fusion retrieves a specific row of data in one session, it doesn't repeat it in the next session. Therefore, when you update a row, you must specify the primary key so that Cold Fusion knows which row to update.
There are two ways to update rows in a table. The code in Listing 12.8 demonstrates a row update using the SQL UPDATE statement.
See the section in Chapter 9, "SQL Data Manipulation," entitled "Modifying Data," for a detailed discussion of the SQL UPDATE statement.
<CFQUERY
DATASOURCE="A2Z"
>
UPDATE Employees
SET FirstName='#FirstName#',
MiddleInit='#MiddleInit#',
LastName='#LastName#',
Title='#Title#',
PhoneExtension='#PhoneExtension#',
EMail='#EMail#'
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
<CFOUTPUT>
<HTML>
<HEAD>
<TITLE>Employee #LastName#, #FirstName# Updated</TITLE>
</HEAD>
<BODY>
<H1> Employee #LastName#, #FirstName# Updated </H1>
</BODY>
</HTML>
</CFOUTPUT>
This SQL statement updates the six specified rows for the employee whose ID is the passed EmployeeID.
To test this update template, execute template EMPUPD1.CFM with different EmployeeID values and then submit your changes.
Hard-coded SQL statements are not flexible or easy to maintain, so Cold Fusion provides a simpler way to update rows in database tables.
The CFUPDATE tag is very similar to the CFINSERT tag we discussed earlier in this chapter. CFUPDATE requires just two attributes, the ODBC data source and the name of the table to update.
Like CFINSERT, the following attributes are available to you:
When using CFUPDATE, Cold Fusion automatically locates the row you want to update by looking at the table to ascertain its primary key. You must ensure that the primary key value is passed, as we did in Listing 12.7 with a hidden field.
The code in Listing 12.9 performs the exact update as in Listing 12.8, but uses the CFUPDATE tag rather than the SQL UPDATE tag. This code is more readable, reusable, and accommodating for future form field changes you might make.
< CFUPDATE DATASOURCE="A2Z" TABLENAME="Employees" > <CFOUTPUT> <HTML> <HEAD> <TITLE>Employee #LastName#, #FirstName# Updated</TITLE> </HEAD> <BODY> <H1> Employee #LastName#, #FirstName# Updated </H1> </BODY> </HTML> </CFOUTPUT>
You'll have to change the FORM ACTION attribute in EMPUPD1.CFM to use EMPUP3.CFM to test this form. Make this change and try updating several employee records.
Just as with adding data, the choice to use CFUPDATE or SQL UPDATE is yours. The guidelines as to when to use each option are similar too.
Here's the rule of thumb:
Unlike adding and updating data, Cold Fusion provides no efficient way to delete data. Delete is always a dangerous operation, and the Cold Fusion developers didn't want to make it too easy to delete the wrong data.
To delete data in a Cold Fusion template, you must use the SQL DELETE statement, as shown in Listing 12.10. The code first checks to ensure that an employee ID was passed. If the EmployeeID field is not present, the operation will terminate. If an employee ID is passed, a CFQUERY is used to pass a SQL DELETE statement to the ODBC data source.
See the section in Chapter 9, "SQL Data Manipulation," entitled "Deleting Data," for a detailed discussion of the SQL DELETE statement.
<CFIF #ParameterExists(EmployeeID)# IS "No"> Error! No EmployeeID was specified! <CFABORT> </CFIF> <CFQUERY DATASOURCE="A2Z" > DELETE FROM Employees WHERE EmployeeID = #EmployeeID# </CFQUERY> <HTML> <HEAD> <TITLE>Employee Deleted</TITLE> </HEAD> <BODY> <H1>Employee Deleted</H1> </BODY> </HTML>
The code
DELETE Employees WHERE EmployeeID = #EmployeeID#
deletes the record for the employee ID passed. If EmployeeID was 7, the code would translate to
DELETE Employees WHERE EmployeeID = 7,
and the employee with an employee ID of 7 would be deleted from the Employees table.
You can now add to, update, and delete from your Employees table.
Just when you thought you could relax and take a day off, human resources needs you to provide access to additional table columns.
You, therefore, start to modify both the "employee add" and the "employee update" forms. You make sure that the additional five fields are added to both templates, in the same order, spelled the same way, and are exactly the same length.
You then realize that you are doing everything twice. There is really very little difference between the add and update forms, except that one needs existing values pre-filled for updating. The form itself is identical.
With all the effort that we have taken in the past few chapters to prevent any duplication of effort, this seems quite counterproductive--and indeed it is.
Using conditional expressions, you can create a single form that can be used for both adding and updating data. A new and improved add and update form is shown in Listing 12.11.
<CFIF #ParameterExists(EmployeeID)# IS "No">
<CFSET #NewRecord# = "Yes">
<CFELSE>
<CFSET #NewRecord# = "No">
</CFIF>
<CFIF #NewRecord# IS "No">
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employee"
>
SELECT FirstName,
MiddleInit,
LastName,
Address1,
Address2,
City,
State,
Zip,
Title,
PhoneExtension,
EMail
FROM Employees
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
</CFIF>
<HTML>
<HEAD>
<CFIF #NewRecord# IS "Yes">
<TITLE>Add an Employee</TITLE>
<CFELSE>
<CFOUTPUT QUERY="Employee">
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>
</CFOUTPUT>
</CFIF>
</HEAD>
<BODY>
<H1>
<CFIF #NewRecord# IS "Yes">
<TITLE>Add an Employee</TITLE>
<CFELSE>
<CFOUTPUT QUERY="Employee">
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>
</CFOUTPUT>
</CFIF>
</H1>
<FORM ACTION="empau2.cfm" METHOD="POST">
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
</CFOUTPUT>
</CFIF>
<P>
First name:
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(FirstName)#"</CFOUTPUT>
</CFIF>
>
Middle Initial:
<INPUT TYPE="text" NAME="MiddleInit" SIZE="1" MAXLENGTH="1"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(MiddleInit)#"</CFOUTPUT>
</CFIF>
>
<BR>
Last name:
<INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(LastName)#"</CFOUTPUT>
</CFIF>
>
<BR>
Address:
<INPUT TYPE="text" NAME="Address1" SIZE="50" MAXLENGTH="50"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Address1)#"</CFOUTPUT>
</CFIF>
>
<BR>
<INPUT TYPE="text" NAME="Address2" SIZE="50" MAXLENGTH="50"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Address2)#"</CFOUTPUT>
</CFIF>
>
<BR>
City:
<INPUT TYPE="text" NAME="City" SIZE="40" MAXLENGTH="40"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(City)#"</CFOUTPUT>
</CFIF>
>
<BR>
State:
<INPUT TYPE="text" NAME="State" SIZE="5" MAXLENGTH="5"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(State)#"</CFOUTPUT>
</CFIF>
>
<BR>
Zip:
<INPUT TYPE="text" NAME="Zip" SIZE="10" MAXLENGTH="10"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Zip)#"</CFOUTPUT>
</CFIF>
>
<BR>
Title:
<INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="20"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Title)#"</CFOUTPUT>
</CFIF>
>
<BR>
Phone Extension:
<INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(PhoneExtension)#"</CFOUTPUT>
</CFIF>
>
<BR>
E-Mail:
<INPUT TYPE="text" NAME="EMail" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(EMail)#"</CFOUTPUT>
</CFIF>
>
<P>
<CFIF #NewRecord# IS "Yes">
<INPUT TYPE="submit" VALUE="Add Employee">
<CFELSE>
<INPUT TYPE="submit" VALUE="Update Employee">
</CFIF>
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
Let's now analyze Listing 12.11. The first thing we did was to determine if an insert or an update is required. How can we know that? For a record to be updated, an employee ID must be passed, otherwise Cold Fusion would have no idea which record to update. It makes no sense to pass an employee ID when adding a row because the new employee's ID will be assigned when the data actually inserts into the table.
We can, therefore, make a safe assumption that if an employee ID is present, it is an update operation; if not, it's an addition.
The first line in the template,
<CFIF #ParameterExists(EmployeeID)# IS "No">,
checks to see if EmployeeID was specified and sets a variable called #NewRecord# to "Yes" or "No" based on its existence. The code
<CFSET #NewRecord# = "Yes">
sets #NewRecord# to "Yes" because the
#ParameterExists(EmployeeID)#
test returned "No". If
#ParameterExists(EmployeeID)#
returns "Yes", the code
<CFSET #NewRecord# = "No">
sets #NewRecord# to "No".
Either way, once the first five lines of the template have been processed, we'll have a new variable called #NewRecord# that indicates whether or not a new record is being added. We can then use this variable throughout the template wherever different code is needed for insertions or updates.
NOTE: Cold Fusion variables are special fields that you can create at any time, and they can contain any values. Once a variable creates during the processing of a template, it is available for use until that processing is complete. Variables are assigned using the CFSET tag and can be reassigned using that same tag as needed.
Even the CFQUERY that retrieves the record of the employee to update is conditional. It would make no sense to try to retrieve a record that does not yet exist in the table so the entire CFQUERY statement is enclosed in a CFIF statement. The code
<CFIF #NewRecord# IS "No">
ensures that everything until the matching </CFIF> is processed only if this is an update.
The page title is also conditional so that it accurately reflects the operation that is being performed. If the operation is an update, the name of the employee being updated is displayed. Displaying the employee name requires displaying dynamic data, so the title is enclosed within a CFOUTPUT block as next shown if the operation is an update.
<CFOUTPUT QUERY="Employee"><TITLE>Update an Employee - #LastName#, FirstName#</TITLE></CFOUTPUT>
The very first field in the form itself is a hidden field. The code
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
creates a hidden field containing the primary key of the record to be updated. This is required for the CFUPDATE tag to work, as explained earlier in this chapter.
This hidden field is only wanted if the operation is an update. For insert operations, a new employee ID generates automatically at the time of data insertion so the whole INPUT tag is conditional and is only processed if the <CFIF #NewRecord# IS "No"> condition returns true. If #NewRecord# is "Yes", all the code until the matching </CFIF> tag is ignored.
Next comes all the fields themselves, starting with the FirstName field. When adding a new record, the FirstName input field needs to look like this:
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30">
When updating a record, the same field needs one additional attribute, VALUE. The para-meter passed to the VALUE attribute is the FirstName column as retrieved by the CFQUERY tag. The complete field for an update operation looks like:
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30" VALUE="#Trim(FirstName)#">
Because the only difference between the two is the VALUE attribute, we can make that conditional too. This typically involves breaking the INPUT field up over multiple lines, but that is allowed and will not break anything. As shown below, the basic INPUT tag is first defined, but no terminating > is provided yet. Instead, we test to see if this is an update, and if "yes," the VALUE attribute is included within a CFOUTPUT block so that it may be populated with the current value. The terminating > appears after the condition, ensuring that the VALUE attribute will be contained within the INPUT tag if it is needed.
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30" <CFIF #NewRecord# IS "No"> <CFOUTPUT QUERY="Employee">VALUE="#Trim(FirstName)#"</CFOUTPUT> </CFIF> >
What is the downside to creating conditional INPUT tags like this? There are none. The INPUT tag is a little more complicated to read, and the generated HTML source code will likely contain multiple lines and blank lines in the middle of the tag, but neither of those are really downsides. The upside is that if you need to change any tag attributes, you only have to make the change once. Similarly, if you need to add input fields, you only have to make changes to a single template.
The final conditional code in Listing 12.11 is the submit button. Again, we check the value of the #NewRecord# variable to specify an appropriate value for the submit button text.
You can now try out this form. If you specify an EmployeeID parameter in the URL, like &EmployeeID=7, you'll be presented with an update form. Executing the same template without an EmployeeID parameter displays an "add" form. Both forms are shown in Figure 12.6 and Figure 12.7.
Don't submit the form yet because we have to create a template that can conditionally perform the actual insert or update.
Figure 12.6 Templates can be reused when using conditional code, and this add form is also an update form.
Figure 12.7 Templates can be reused when using conditional code, and this update form is also an add form.
Processing a conditional form requires the destination template to ascertain which operation needs to be performed. There are many ways to do this including:
Embedding a hidden field in the form that specifies the operation.
Checking for the existence of a specific field, or the lack thereof (similar to what we did in Listing 12.11).
Checking the value of a known entity, for example the submit button, which could have a different value based on the operation being performed.
CAUTION: To submit the value of a button, you must name the field with the INPUT NAME attribute. This way, the browser can submit a name=value pair for the submit button.
Not all browsers submit the value specified in a INPUT field of type submit, even if the NAME attribute is provided. If you do need to support older and less commonly used browsers, you should avoid relying on submit button values.
For our example, we'll do the same thing that we did in the form template itself--check for the existence of an EmployeeID. The hidden EmployeeID field will only be present if the operation is an update.
<CFIF #ParameterExists(FORM.EmployeeID)# IS "No"> <CFSET #Operation# = "Inserted"> < CFINSERT DATASOURCE="A2Z" TABLENAME="Employees" > <CFELSE> <CFSET #Operation# = "Updated"> < CFUPDATE DATASOURCE="A2Z" TABLENAME="Employees" > </CFIF> <CFOUTPUT> <HTML> <HEAD> <TITLE>Employee #FORM.LastName#, #FORM.FirstName# #Operation#</TITLE> </HEAD> <BODY> <H1>Employee #FORM.LastName#, #FORM.FirstName# #Operation#</H1> </BODY> <HTML> </CFOUTPUT>
The conditional insert and update template is shown in Listing 12.12. Just as in the form in Listing 12.11, the first thing we do is to check for the presence of the EmployeeID field, but this time we explicitly check for an EmployeeID field within a FORM. For an update to work, the primary key form field must be present. Chances are, they would not be any other EmployeeID field present, but just to make sure, we preface the field name with the FORM identifier, as follows:
<CFIF #ParameterExists(FORM.EmployeeID)# IS "No">
If the EmployeeID does not exist, we execute a CFINSERT; otherwise we execute a CFUPDATE. It's that simple. You now have a single template that can both insert and update employee records.
Unlike in Listing 12.11, we did not set a variable to indicate which operation to perform. Why not? In Listing 12.11, there were many conditional elements within the code, so to eliminate repeatedly checking for parameter existence, a variable was created which is checked instead. In this example, there is only one conditional code block, so it is easy to perform the insert or update operations directly within it.
The other thing we did in the conditional code is to set a variable called #Operation#, which is either set to "Inserted" or "Updated". We then used this variable twice later on, in the title and in the body. This way, we did not need to create two more conditional code blocks. The variable contains the appropriate text to automatically display wherever it is used.
NOTE: An unlimited number of ways can structure your conditional code, and no single approach is right or wrong. The examples in this chapter demonstrate several different techniques, but you will undoubtedly develop several of your own. The only rule to remember is to make your code readable, manageable, and wherever possible, reusable.
Now that we've created add, modify, and delete templates, let's put it all together and create a finished application.
The following templates are a combination of all that we have learned in both this chapter and the previous chapter.
The template shown in Listing 12.13 is the main employee administration page. It displays all the employees in the Employees table and provides links to edit and delete them, as well as to add a new employee. The administration page is shown in Fig- ure 12.8.
See the section, "Displaying results in tables," in Chapter 10, "Cold Fusion Basics," for details on how to create table containing dynamic data.
Listing 12.14 is essentially the same reusable employee add and update form as we created earlier. The only significant change is that the FORM ACTION has been changed so that template EMPAU4.CFM processes the responses.
Listing 12.15 and Listing 12.16 perform the actual data insertions, updates, and deletions. The big change in these templates is that they themselves provide no user feedback at all. Instead, as soon as they finish processing the database changes, they return to the administration screen using the <CFLOCATION> tag. <CFLOCATION> is used to redirect the user to another URL.. The following sample code instructs Cold Fusion to display the empadmin.cfm template upon completion of the database operation.
<CFLOCATION URL="empadmin.cfm">
In this way, as soon as any change competes, the updated employee list displays and is ready for further processing.
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER>
<TR>
<TH COLSPAN=3>
<H1>Employees</H1>
</TH>
</TR>
<TR>
<TH>
Name
</TH>
<TH>
Extension
</TH>
</TR>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
#LastName#, #FirstName#
</TD>
<TD>
Ext: #PhoneExtension#
</TD>
<TD>
<A HREF="empau3.cfm?EmployeeID=#EmployeeID#">Edit</A>
<A HREF="empdel2.cfm?EmployeeID=#EmployeeID#">Delete</A>
</TD>
</TR>
</CFOUTPUT>
<TR>
<TH COLSPAN=3>
<A HREF="empau3.cfm">Add an Employee</A>
</TH>
</TR>
</TABLE>
</CENTER>
</BODY>
</HTML>
Figure 12.8 The employee administration page is used to add, edit, and delete employee records.
){kind=link}
<CFIF #ParameterExists(EmployeeID)# IS "No">
<CFSET #NewRecord# = "Yes">
<CFELSE>
<CFSET #NewRecord# = "No">
</CFIF>
<CFIF #NewRecord# IS "No">
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employee"
>
SELECT FirstName,
MiddleInit,
LastName,
Address1,
Address2,
City,
State,
Zip,
Title,
PhoneExtension,
EMail
FROM Employees
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
</CFIF>
<HTML>
<HEAD>
<CFIF #NewRecord# IS "Yes">
<TITLE>Add an Employee</TITLE>
<CFELSE>
<CFOUTPUT QUERY="Employee">
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>
</CFOUTPUT>
</CFIF>
</HEAD>
<BODY>
<H1>
<CFIF #NewRecord# IS "Yes">
<TITLE>Add an Employee</TITLE>
<CFELSE>
<CFOUTPUT QUERY="Employee">
<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>
</CFOUTPUT>
</CFIF>
</H1>
<FORM ACTION="empau4.cfm" METHOD="POST">
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
</CFOUTPUT>
</CFIF>
<P>
First name:
<INPUT TYPE="text" NAME="FirstName" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(FirstName)#"</CFOUTPUT>
</CFIF>
>
Middle Initial:
<INPUT TYPE="text" NAME="MiddleInit" SIZE="1" MAXLENGTH="1"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(MiddleInit)#"</CFOUTPUT>
</CFIF>
>
<BR>
Last name:
<INPUT TYPE="text" NAME="LastName" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(LastName)#"</CFOUTPUT>
</CFIF>
>
<BR>
Address:
<INPUT TYPE="text" NAME="Address1" SIZE="50" MAXLENGTH="50"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Address1)#"</CFOUTPUT>
</CFIF>
>
<BR>
<INPUT TYPE="text" NAME="Address2" SIZE="50" MAXLENGTH="50"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Address2)#"</CFOUTPUT>
</CFIF>
>
<BR>
City:
<INPUT TYPE="text" NAME="City" SIZE="40" MAXLENGTH="40"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(City)#"</CFOUTPUT>
</CFIF>
>
<BR>
State:
<INPUT TYPE="text" NAME="State" SIZE="5" MAXLENGTH="5"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(State)#"</CFOUTPUT>
</CFIF>
>
<BR>
Zip:
<INPUT TYPE="text" NAME="Zip" SIZE="10" MAXLENGTH="10"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Zip)#"</CFOUTPUT>
</CFIF>
>
<BR>
Title:
<INPUT TYPE="text" NAME="Title" SIZE="20" MAXLENGTH="20"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(Title)#"</CFOUTPUT>
</CFIF>
>
<BR>
Phone Extension:
<INPUT TYPE="text" NAME="PhoneExtension" SIZE="4" MAXLENGTH="4"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(PhoneExtension)#"</CFOUTPUT>
</CFIF>
>
<BR>
E-Mail:
<INPUT TYPE="text" NAME="EMail" SIZE="30" MAXLENGTH="30"
<CFIF #NewRecord# IS "No">
<CFOUTPUT QUERY="Employee">VALUE="#Trim(EMail)#"</CFOUTPUT>
</CFIF>
>
<P>
<CFIF #NewRecord# IS "Yes">
<INPUT TYPE="submit" VALUE="Add Employee">
<CFELSE>
<INPUT TYPE="submit" VALUE="Update Employee">
</CFIF>
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
<CFIF #ParameterExists(FORM.EmployeeID)# IS "No"> < CFINSERT DATASOURCE="A2Z" TABLENAME="Employees" > <CFELSE> < CFUPDATE DATASOURCE="A2Z" TABLENAME="Employees" > </CFIF> <CFLOCATION URL="empadmin.cfm">
<CFIF #ParameterExists(EmployeeID)# IS "No"> Error! No EmployeeID was specified! <CFABORT> </CFIF> <CFQUERY DATASOURCE="A2Z" > DELETE FROM Employees WHERE EmployeeID = #EmployeeID# </CFQUERY> <CFLOCATION URL="empadmin.cfm">
The conditions we have created so far only test for equality, that is, they have perform a check to determine if a field or value equals another value. You are not limited to testing for equality. Cold Fusion provides a complete set of conditional operators, so you can combine conditions with AND and OR operators too.
The complete list of operators is shown in Table 12.1. Notice that many of the operators have alternate syntax, and you may use whichever syntax you are comfortable with. The syntax GREATER THAN OR EQUAL is very descriptive, but is also very wordy and takes up additional screen space which might force you to scroll your screen or wrap text over multiple lines. The abbreviated syntax GTE accomplishes the exact same thing and takes far less space, but is also less intuitive.
| Operator | Alternate Description |
| IS | EQUAL, EQ--Check that the right value is equal to the left value. |
| IS NOT | NOT EQUAL, NEQ--Check that the right value is not equal to the left value. |
| CONTAINS | Check that the right value is contained within the left value. |
| DOES NOT CONTAIN | Check that the right value is not contained within the left value. |
| GREATER THAN | GT--Check that the left value is greater than the right value. |
| LESS THAN | LT--Check that the left value is less than the right value. |
| GREATER THAN | GTE--Check that the left value is greater than or equal to the right value. |
| LESS THAN OR EQUAL | LTE--Check that the left value is less than or equal to the right value. |
The Boolean operators available to you are shown in Table 12.2.
| Operator | Description |
| AND | Conjunction, returns TRUE only if both expressions are true. |
| OR | Disjunction, returns TRUE if either expression is true. |
| NOT | Negation |
When combining conditions, each condition must be contained within a set of parentheses. The following example checks to see if both the FirstName and LastName fields exist:
<CFIF (#ParameterExists(FirstName)#) AND (#ParameterExists(LastName)#)>
To check for either a first name or a last name, you could do the following:
<CFIF (#ParameterExists(FirstName)#) OR (#ParameterExists(LastName)#)>
You will often want to verify that a field is not empty, and that it does not contain blank spaces. The following condition demonstrates how this can be accomplished:
<CFIF #Trim(LastName)# IS NOT "">
To check if a value is within a range of values, you can use the CONTAINS operator:
<CFIF "KY,MI,MN,OH,WI" CONTAINS #State#>
or:
<CFIF #TaxableStates# CONTAINS #State#>
More complex expressions can be created by combining conditions within parentheses. For example, the following condition checks to see if a payment is by check or credit card. If payment is by credit card, it checks to ensure that there is an approval code:
<CFIF (#PaymentType# IS "Check") OR ((#PaymentType# IS "Credit") AND (#ApprovalCode# IS NOT ""))>
As you can see, the Cold Fusion conditional support is both extensive and powerful. We'll be making much more use of the CFIF tag in upcoming chapters.
This chapter introduced data entry via Cold Fusion forms. You learned how to insert, update, and delete rows from ODBC data sources, and learned how to use the Cold Fusion CFINSERT and CFUPDATE tags to create simpler and more powerful database processing templates. You also learned when not to use these tags. Reusable code was introduced, and we walked through several examples that demonstrated code reuse techniques. In doing so, we created an entire employee management application. Finally, you learned how to use the Cold Fusion CFIF tag and conditional operators.
© Copyright, Macmillan Computer Publishing. All rights reserved.