Retrieving the Form Data Using ColdFusion MX | ColdFusion MX Professional Projects

You can use SQL queries to retrieve records from a database table. There are many instances when you'll want to retrieve data based on certain end user-specified criteria. For example, you might want to retrieve records for everyone in a particular department or in a particular city whose first name is Arnold. You can use forms in ColdFusion applications to allow end users to specify the data that's to be retrieved in a query. As discussed earlier, when you submit a form, the variables pass to the action page where processing takes place.

The form is populated with data from a database in two steps:

<CFQUERY> gets the data from the database.
<CFOUTPUT> sets the default values for the fields.

Using the <CFQUERY> Tag

The <CFQUERY> tag executes a SQL SELECT statement. The syntax for the <CFQUERY> tag is

 <CFQUERY DATASOURCE="data_source_name" NAME="query_name">       SQL SELECT statement </CFQUERY>

The DATASOURCE attribute is assigned the name of the ODBC connection, data source. The NAME attribute gives a unique name for the query. Query naming rules are the same as variable naming rules. After its execution, the result of the SELECT statement is stored in the server's memory as a query object.

When you submit a form to a ColdFusion template, a series of variables related to the fields in the form are created. These variables are accessed as Form.FieldName.

Use <CFINSERT> or <CFUPDATE> to add to or update the database. Alternately, you can build an SQL INSERT or UPDATE statement manually and then use <CFQUERY> to perform the operation. Here are the steps to follow for these operations:

Present a blank form for insertion of a new record into the database.
When the blank form is submitted, the record should be inserted or updated.
There may be a request to retrieve a particular record from the database. The record needs to be displayed in a form.

When the value of the URL. Action is Load, existing records from the database are loaded. <CFQUERY> selects the record from the database.

The insertion of a new record follows these two steps:

Insert the record using <CFQUERY>.
Recall the record to display it for editing and updating. Use SQL statements in <CFQUERY> to do this.

The updating of a record follows a similar process. First, the value of URL.Action is determined. Then the record is updated. Finally, the updated record is recalled.

Using the <CFLOOP> Tag and QUERY

QUERY loops implement a repetition flow-control structure. The syntax for the QUERY loop is

 <CFLOOP QUERY="query_name">     Statements to be executed for each record returned by the query </CFLOOP>

The STARTROW and ENDROW attributes of the <CFLOOP> tag specify the starting and ending row to be processed. All the records in a query are numbered sequentially, starting at 1. Processing starts and ends at the specified rows. The syntax is

 <CFLOOP QUERY="query_name" STARTROW="startrow_value" ENDROW="endrow_value">     Statements to be executed for each record returned by the query </CFLOOP>

You can generate a columnar report, which extracts data from all columns of a table and displays the results one row at a time. A query loop processes one record because STARTROW and ENDROW are both assigned the value 1. Each field retrieved from the table creates a new variable. Variables query_name.fieldname are updated with field values of each row.

You can also add buttons or images to your Web page to navigate through a series of records. First, use a parameter to specify the current record number to display. Then, create variables to calculate the previous and next record number using the <CFSET> tag.

 <CFSET nextRec = displayRecNo + 1> <CFSET prevRec = displayRecNo - 1>

Note

Ensure that you do not pass an illegal record number—a value less than 0 or greater than the total number of records. Use the <CFIF> tag to display links only when prevRecNo is greater than 0 and nextRecNo is less than the total number of records.

To determine the total number of records in a recordset returned by a query, use the query_name.recordcount variable.

Using Forms to Perform Data Manipulation

Earlier you learned about viewing and searching a database using forms. Adding data to a database involves two steps: displaying a form to collect the data, and submitting this data to ColdFusion for processing.

You can use SQL INSERT to add the data to the database, but ColdFusion offers a more effective command, <CFINSERT>. This command simplifies the process of adding data to your database. Specify the data source name and the table name. You have the option of supplying the FORMFIELDS parameter to specify the exact fields to add. If this parameter isn't specified, it inserts all the fields. The syntax of <CFINSERT> is as follows:

 <CFINSERT DATASOURCE = "dsrc_name" TABLENAME = "tbl_name"           FORMFIELDS = "formfield1, formfield2, ...">

At times, you may not want ColdFusion to add certain form fields because they're not actually part of a database table. (These hidden fields are part of a security system.) Specify this using the FORMFIELDS parameter.

You can also use FORMFIELDS while collecting data that needs to be added to multiple tables. In this case, create a template that has two or more <CFINSERT> statements using FORMFIELDS.

Updating data is very similar to adding data to a database. The two templates required are a data entry form and an update template. To update the database, retrieve the data to be updated from the table by using a standard <CFQUERY>. Pass the retrieved values as attributes to the HTML form.

Using <CFUPDATE> makes the updating of table data much easier. Specify the name of the data source and the table. Supply the FORMFIELDS parameter to specify the exact fields that you want to add. The syntax is given as follows:

 <CFUPDATE DATASOURCE = "dsrc_name" TABLENAME = "tbl_name"          FORMFIELDS = "fornfield1, formfield2, ...">

ColdFusion doesn't provide a specific function for deleting data. You must use SQL DELETE for this function.