Thursday, August 21, 2008 5:09 AM
Inserting data into a database

This tutorial will show you how to insert data into your database by using ColdFusion Markup Language. 

The first thing I will explain is that you will have a form page and an action page.  The form page is the page where the user will type in the data and the action page is where ColdFusion will insert the data into the database.

Let's begin by making the form page.

Create a new page and call it "form.cfm".

Then type in this code on it.

<HTML>
    <HEAD>
         <TITLE>This is the form page</TITLE>
    </HEAD>

    <BODY>

     <FORM ACTION="action.cfm" METHOD="post">
     <input type="text" name="firstname" value=""> - First Name<BR>
     <input type="text" name="lastname" value=""> - Last Name<BR>
     <input type="text" name="email" value=""> - Email<BR>
     <textarea name="message" rows="7" cols="40"></textarea><BR>
     <font size="1" face="verdana">Additional Message</font>
     <input type="submit" name="Submit" value="">
     </FORM>

    </BODY>
</HTML>

The code above will create a form that will allow a visitor to type in their first name, last name, email address and a brief message to you. Think of it as a contact form so your customers can send you an email to ask you a question. When the user clicks on the "submit" button, they will then be directed to the "action.cfm" page with all of the information they have typed in. The "action.cfm" is the page that will use ColdFusion server to process the information, save it to a database and then send you an email copy of the visitors submission.

Create a new page and call it "action.cfm"

The "action.cfm" page consists of both regular HTML code and ColdFusion processing code. here's how this page is made:

<HTML>
    <HEAD>
       <CFOUTPUT>
           <TITLE>Thank you #firstName# #Lastname# for your submission!</TITLE>
       </CFOUTPUT>
    </HEAD>

    <BODY>

    <!--- First let's insert this data into our database for safe keeping and later usage. --->
     <CFINSERT DATASOURCE="YourDSN" TABLENAME="TableName" FORMFIELDS="firstname, lastname, email, message">

     <!--- Now let's send a copy of this submission by email to ourselves --->
     <CFMAIL FROM="#form.email#" TO="youremail@yourisp.com" SUBJECT="Submission from website!" SERVER="mail.yoursite.com" PORT="25">
      There has been a form submission on your site, here's what they had to say:

      Sender name: #form.firstname# #form.lastName#
      Sender email: #form.email#
      Sender IP: #REMOTE_ADDR#

      Message: #Message#
      Message Sent: #DateFormat(now(), 'mmmm dd, yyyy')# #TimeFormat(Now(), 'hh:mm:ss tt')#
      
    </CFMAIL>

     <!--- Now Let's Send An Email To The Person Submitting This Form, Thanking Them For The Form Submission --->
     <CFMAIL FROM="youremail@yourisp.com" TO="#form.email#" SUBJECT="Thank you for submitting our form!" SERVER="mail.yoursite.com" PORT="25">
      Dear #form.firstname# #form.Lastname#,
           Thank you for submitting Our Contact Form. We will contact you back within 24 hours. We've also
      sent you a copy of what you submitted below.

      Thanks again,
      Your Name
      Your Company

      Submission Receipt:
      ===========================================================
      Sender name: #form.firstname# #form.lastName#
      Sender email: #form.email#
      Sender IP: #REMOTE_ADDR#

      Message: #Message#
      Message Sent: #DateFormat(now(), 'mmmm dd, yyyy')# #TimeFormat(Now(), 'hh:mm:ss tt')#
      
    </CFMAIL>

     <!--- Now we actually have to display something on the page to let the user know that his submission was successful --->
     <CFOUTPUT>

     Thank you #form.firstname# #form.lastname#, we have successfully received your request and it's been sent to the coresponding
     support personnel.  Expect to hear from us within 24 hours.  A copy of this form submission was also sent to the email address you specified as being
     yours in the previous page for your records.<BR>
     <BR>
     Thanks,<BR>
     Your Name<BR>
     Your Company
     </CFOUTPUT> 

    </BODY>
</HTML>

Now, let me explain what the code above does in detail.

First notice that on the heading section of the page, right before and after the <title></title> tags, I put a <CFOUTPUT> tag, this was done so that you could customize this users experience by putting their name on the tile of the page.  Keep in mind that anything between the <CFOUTPUT> tags with # around it will be processed and a value will be returned, in this occasion it was the person's first and last name.

The next thing I'd like you to notice is the tag <CFINSERT>, this tag is a ColdFusion internal processing tag that allows you to insert the data into your database much easier and faster then the traditional way of using <CFQUERY>. The tag requires a few values to be set for it to correctly function. Here is what they are:

DATASOURCE="YourDSN" - the datasource is the name of your ODBC connection to your database. If you are unsure of this, please consult the ColdFusion documentation.
TABLENAME="TableName" - This is the name of the table that you will be using to insert the records into. You can name it anything you'd like, but it must be in the database that is mapped with the datasource specified above.
FORMFIELDS="firstname, lastname, email, message" - This is the field in the form that you actually want to input into the database.  This is optional, but highly recommended that you use. The reason being is that if you would have named the submit tag to "SendButton" and there was no field in your table specified above called "SendButton" you'd receive an error.  So please always use the "formfields" option to prevent errors.

The next things we did was use the <CFMAIL> tag.  This is a tag that tells ColdFusion that you want to send an email message from your site.
This tag requires a few value also as you noticed above. Here is what those values are:
FROM - This is the field that let's ColdFusion know who the sender of the email is, in case the person receiving the email wants to reply to this email.
TO - This is the field that let's ColdFusion know where the email is to be delivered.
SUBJECT - This is the subject of the message.
SERVER - This is the mail server you'd like to use to send the email.  This is also an optional field, but highly recommended that you use.
PORT - This is the port that the mail server specified above is listening on to send the email. This field is optional, but becomes required when you specify a SERVER.

A few functions I used within the form are:
#remote_addr# - this is to get the users IP address and include it on your email.
#DateFormat# - The date format function allows you to specify and format the date according to your needs.  In this case we're using the format:
mmmm dd, yyyy which translates to: May 1, 2001.  
#TimeFormat# - This allows you to take the current time and format it according to your needs. In this case we used the format:
hh:mm:ss tt which translates to: 3:35:23 PM
now() - this was specified within the DateFormat and TimeFormat tags above, it returns the current date/time for formatting with the tags above.

The last tag we've used is the <CFOUTPUT> tag. Like I mentioned before, this is the tag that ColdFusion uses to process values. Without this tag, the field "form.firstname# would not get processed and would therefore be displayed as #form.firstname# instead of "bob".

NOTE (for your information, not related to tutorial):
A question I always get is:
If ColdFusion needs <CFOUTPUT> to process values, why is it that the <CFMAIL> and the <CFUPDATE> do not need to be within the <CFOUTPUT> tag?

My answer always is:
Some ColdFusion tags such as <CFMAIL> and <CFINSERT> have capabilities of executing <CFOUTPUT> type actions.  Think of them as having a built-in <CFOUTPUT> in their processing system.  Therefore they do not need to have <CFOUTPUT>'s around them to process for values!

You have successfully created a submit form that inserts data into a database and then sends an email to you and your visitor at the same time.



All ColdFusion Tutorials By Author: Pablo Varando
Copyright © 2008 People 360, Inc.
You are 1 of 308 active visitors!
ColdFusion Hosting