Rapidly deploy form submission, By Doug Hyde

Have you ever wanted to have a quick way to process just about any form in coldfusion? Whether it is by direct submission of a form by submit or through AJAX, you can apply some simple standards to your database and to your form elements, then this tutorial makes it easy for you to develop and deploy forms in your application.

Note, some developers will frown on it because it does not draw on stored procedures where speed can be gained in your application, but sometimes you just want to get it working without writing a lot of code. As my role is commonly to build prototypes, I have found coding forms to be a tedious and repetitive task, and I often leave the task to a more hard core development team. For me, the approach set out below cuts the effort to deploy a simple application in half. And because you aren't writing a separate set of code to process every form, it keeps your code efficient. It also allows you to do server side verification of data before submitting it to the database.

Note to use this tutorial, you must adopt or create the following standards in your database and your form elements. The tutorial assumes you know how to register a database in coldfusion or bluedragon.

Database standards – For the purpose of this tutorial, all fields in the database that need to be distinguished by the first three characters of the column name - eg for text values, the name is txtColumnName; for numeric or integer values, numValue or intValue define them as such. In addition, table names must begin with a three letter extension, such as tbl or other extension as defined by you in the configuration at the top. You can develop your own standard to help improve security, but you will need to adjust Listing 2 below in this case. We will call the datasource here “easyform” and have used it below for clarity, though in practice it is best passed as a variable rather than coded into the listing. To test this tutorial out, you could create an MS Access database named “easyform” with a single table named tbluser, and with four columns and at least one record as follows:

  intuser (primary key, autoincrement)
  txtfirstname (text)
  txtlastname (text)
  txtphonenumber (text)

Standards in naming form elements - Each of your form elements on the submitting form must be made up of three elements separated by a '$' sign: (1) the table name where the form element will be stored, without the extension (you can use whatever you wish, though for this tutorial, "table" is used), (2) the full column name in the database, (3) the primary key if it is an update, or the values "0" or "new" if you need to insert a new record. For example, a form element named ‘user$txtCompanyName_1$102' will update the table, and a form element named 'user$txtCompanyName_1$new' will insert a new record. By using a “$” in the name of the form element, coldfusion can parse the name as a list, allowing you to get information about which table, which column in the table, and the unique identifier. For example,

<cfset targettable = listfirst(‘user$txtCompanyName_1$102’,’$’)>
<cfset column = listgetat(‘user$txtCompanyName_1$102’,2,’$’)>
<cfset uniqueID = listlast(‘user$txtCompanyName_1$102’,’$’)>

Once you determine that the target table is “user” you can append your unique table extension to the front of this new variable, for example:

<cfset tableextension = ‘tbl’>
<cfset table = tableextension & targettable>

To make listing 2 (process_form.cfm) work, one form input element must be inserted for the primary key of each table that will be updated. The element must identify the primary key in the format as 'company$primarykeyname$ID'. Finally, in some cases, you may need to process form elements uniquely, using a separate template or code. If this is the case, the form element (input or textarea) values must include "out$" as the beginning of every name, for example "out$newselect" – these form values are not processed by listing 2 below. If this is not clear, it will be clearer when you review the listings below.

To show you how it works, let’s try two examples. First, we develop a simple form using the standards above, just to show you how simple it can be. We will call the form “simple_form.cfm” and the template that processes it “process_form.cfm”. In the second example, we submit the same form using AJAX.

Listing 1: Creating a simple form

For our simple form, create a new template and save it as “simple_form.cfm”. The form will be used to allow the user to update their contact information; we will assume the user is the first user in the database (and that the primary key equals 1), though the query below could be more flexible by passing a client or session variable to retrieve information about the user.

Here is the listing:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<!---when the form is submitted, it is processed here --->
<cfif process is "update">
  <cfinclude template="process_form.cfm">
</cfif>

<!--- First, we query the database for the user information --->
<CFQUERY NAME="getUserInfo" DATASOURCE="easyform">
  SELECT a.txtFIrstName, a.txtLastName, a.txtPhoneNumber
  FROM tbluser a
  WHERE a.intUser = 1
</CFQUERY>

<cfoutput>
<!---now we create the form--->
<head>
  <title>Update User Information</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <cfset title= "User Information">
  <cfparam name="Process" default="">
  <cfparam name="close" default="no">
</head>

<body>
<!---note, we will use cfform to allow client side validation for this example--->
<cfform name="addedit" method="post" action=”simple_form.cfm”>

<!---declare the unique identifier for the record, if not a new record; for new records, this entry is not needed, and all form elements should be named with “new” at the end. --->
<input type="hidden" name="user$intUser$ID" VALUE="#getuserinfo.intuser#">

<table cellspacing="0" cellpadding="0" border="0" width="640">
  <tr>
    <td colspan="2"><h2>User Contact Information</h2></td>
  </tr>
  <tr>
    <td>First Name:</td>
    <td><cfinput type="text" name="user$txtfirstname$#getuserinfo.intuser#" value="#getuserinfo.txtfirstname#" size="25" required="yes"></td>
  </tr>
  <tr>
    <td>Last Name:</td>
    <td><cfinput type="text" name="user$txtlastname$#getuserinfo.intuser#" value="#getuserinfo.txtlastname#" size="25" required="yes"></td>
  </tr>
  <tr>
    <td>Phone Number:</td>
    <td><cfinput type="text" name="user$txtphonenumber$#getuserinfo.intuser#" value="#getuserinfo.txtphonenumber #" size="25" validate="telephone"></td>
  </tr>
  <tr>
    <td colspan=”2”><input type="submit" value="submit" /> </td>
  </tr>
</table>
</cfform>
</body>
</cfoutput>
</html>

Listing 2: A template to process any form

Now that we have the form built, let’s build the template to process the form, in fact to process any form with elements named using our conventions. Note, this template can be used to update very complex form, for example, where you create an array of form elements, or where you include form elements related to a number of tables, not just one (tbluser) as set out in our example above. It is built on the notion that form elements are sent as a comma-delimited list of values. In some cases, where you need for whatever reason to process form elements outside of the listing below, naming the form element with “out” it is not processed.

Create a new template and save it as “process_form.cfm”, then cut and paste the code below.

<!---configuration to allow your standards to be applied, and to allow submission to multiple datasources registered in coldfusion administrator--->
<cfparam name="tblextension" default="tbl">
<!---you would insert your datasource name as registered in coldfusion here--->
<cfparam name="datatarget" default="easyform">
<!---this is to ensure the form is submitted--->
<cfparam name="FieldNames" default="">
<!---we need to create placeholders for all the tables and records in the list of form fields--->
<cfset tablelist = "">
<cfset recordlist = "">

<cfoutput>
  <!---begin processing the fields here; we are going to loop through all form elements that have been submitted--->
  <cfloop list="#FieldNames#" index="a">

    <!---first, we need to get a list of all table names (note for the simple_form.cfm, there is only one, but there could be many)--->
    <cfset candidate = listfirst(a,'$')>
      <cfif candidate neq 'out'>
          <cfif listfindnocase(tablelist,candidate) is 0>
              <cfset tablelist = listappend(tablelist,candidate)>
          </cfif>

      <!---get names and values of primary keys--->
      <cfset intvalue = listgetat(a,3,'$')>
          <cfif intvalue is 'ID'>
                <cfset '#candidate#_ID' = listgetat(a,2,'$')>
            </cfif>
        </cfif>
</cfloop>

<!---now loop back through all form elements to determine whether they are valid, but do so by table--->
<cfloop list="#tablelist#" index="a">

  <!---get all unique records for each table--->
  <cfloop list="#FieldNames#" index="element">
    <!---process elements from one table--->
    <cfif listfirst(element,'$') is a>
      <!---get unique key values--->
      <cfset intvalue = listgetat(element,3,'$')>
          <cfif intvalue is not 'ID'>
            <cfif listfindnocase(recordlist,intvalue) is 0>
              <cfset recordlist = listappend(recordlist,intvalue)>
            </cfif>
         </cfif>
      </cfif>
  </cfloop>

<!---now loop through the records and harvest the fields--->
<cfloop list="#recordlist#" index="r">
  <cfset fieldsfound = "">
  <!---loop through fields--->
  <cfloop list="#FieldNames#" index="element">
    <cfif element does not contain "out">
      <cfset tblname = listfirst(element,'$')>
      <cfset intvalue = listgetat(element,3,'$')>

      <cfif tblname is a and intvalue is r>
        <cfset fieldname = listgetat(element,2,'$')>
        <CFIF IsDefined("#element#")>
          <!---make sure the value passed is acceptable for the field type; you can add additional server side processing here--->
          <CFSWITCH EXPRESSION="#Left(fieldname,3)#">
                  <CFCASE VALUE="dat">
                <!---Make sure value is a date--->
                <CFIF IsDate(Evaluate(element))>
                  <CFSET fieldsfound = ListAppend(fieldsfound,element)>
                </CFIF>
            </CFCASE>
            <CFCASE VALUE="sin">
              <!---Make sure value is a number--->
              <CFIF IsNumeric(Evaluate(element))>
                <CFSET fieldsfound = ListAppend(fieldsfound,element)>
              </CFIF>
            </CFCASE>
            <CFCASE VALUE="txt">
              <CFIF Evaluate(element) IS NOT "">
                <CFSET fieldsfound = ListAppend(fieldsfound,element)>
              </CFIF>
            </CFCASE>
            <CFCASE VALUE="mem">
              <CFIF Evaluate(element) IS NOT "">
                <CFSET fieldsfound = ListAppend(fieldsfound,element)>
              </CFIF>
            </CFCASE>
            <CFDEFAULTCASE>
              <CFIF Evaluate(element) IS NOT "">
                <CFSET fieldsfound = ListAppend(fieldsfound,element)>
              </CFIF>
            </CFDEFAULTCASE>
        </CFSWITCH>
      </CFIF>
    </cfif>
  </cfif>
</cfloop>

<cfset session.fieldsfound = fieldsfound>

<!---need to insert new records--->
<cfif r is 'new' and fieldsfound neq "">
  <cfquery name="qryInsert" datasource="# datatarget #">
    insert into #tblextension##a#
    (intCompany,
    <CFLOOP INDEX="element" LIST="#fieldsfound#">
      <cfset fieldname = listgetat(element,2,'$')>
      #fieldname#
      <CFIF element IS NOT ListLast(fieldsfound)>,</CFIF>
    </CFLOOP>)
  values (#session.intCompany#,
   <CFLOOP INDEX="element" LIST="#fieldsfound#">
    <CFSET tmpValue = evaluate(element)>
    <cfset fieldname = listgetat(element,2,'$')>
    <CFSWITCH EXPRESSION="#Left(fieldname,3)#">
      <CFCASE VALUE="dat">
        #CreateODBCDate(tmpValue)#
      </CFCASE>
      <CFCASE VALUE="int">
        #tmpValue#
      </CFCASE>
      <CFCASE VALUE="sin">
        #tmpValue#
      </CFCASE>
      <CFCASE VALUE="bln">
        #tmpValue#
      </CFCASE>
      <CFDEFAULTCASE>
        '#replace(tmpValue,"'","'")#'
      </CFDEFAULTCASE>
    </CFSWITCH>
    <CFIF element IS NOT ListLast(fieldsfound)>,</CFIF>
  </CFLOOP>)
  </cfquery>

<!---or update existing records--->
<cfelseif isnumeric(r) and fieldsfound neq "">

  <cfquery name="qryUpdate" datasource="#datatarget#">
    UPDATE tbl#a#
    SET
    <CFLOOP INDEX="element" LIST="#fieldsfound#">
      <cfset fieldname = listgetat(element,2,'$')>
      <CFSET tmpValue = evaluate(element)>
      <!---Determine field type and build SQL accordingly.--->
      #fieldname# =
      <CFSWITCH EXPRESSION="#Left(fieldname,3)#">
      <CFCASE VALUE="dat">
        #CreateODBCDate(tmpValue)#
      </CFCASE>
      <CFCASE VALUE="int">
        #tmpValue#
      </CFCASE>
      <CFCASE VALUE="sin">
        #tmpValue#
      </CFCASE>
      <CFCASE VALUE="bln">
        #tmpValue#
      </CFCASE>
      <CFDEFAULTCASE>
        '#replace(tmpValue,"'","'")#'
      </CFDEFAULTCASE>
    </CFSWITCH>
    <CFIF element IS NOT ListLast(fieldsfound)>,</CFIF>
  </CFLOOP>
    WHERE
    #evaluate('#a#_ID')# = #r# and intCompany = #session.intCompany#
</cfquery>

</cfif>
</CFLOOP>
</cfloop>
</cfoutput>

If you have a simple database with columns as set out above registered in coldfusion, try filling in the form, and submitting, and voila, the data will be updated or inserted as the case may be. Note, if you want to add server side processing of field elements, you can do so using regular expressions or other coldfusion functions either when harvesting the fields, or when inserting into the database, depending on your confidence in the data.

There you have it….if you are following the naming conventions, you can use listing 2 to post any form. I have used it to create tables with a matrix of values, and it processes them all. I have also been experimenting with using it to process AJAX posts. In either case, it saves a lot of time coding form processing. Hope you find this helpful.