Continue To Part 2 4

This Guestbook is part one of a two part tutorial. This part deals with the guest book itself the second part will build a administration area for this guestbook. The guest book submits to two tables in a mySql database the first table being for the actual guest book display template and the second table to be used for an opt-in mailing list. 
===============================================================

The database needed for this project will have two tables one for guestbook entries and the second one will be our already existing mail-list table. I know that it is not good database management to repeat data but in this case I think it is important to keep our guestbook entries separate from our mailing list. Our mailing list can become quite large as we might have other opt-in methods factored in for its growth and I think when we go to do mail-outs it probably will run allot smoother and faster if it is not sorting through a bunch of guestbook entries as well. I am using a mySql database and here are the tables I have laid out for this exercise:

CREATE TABLE guests (
    id int(11) NOT NULL auto_increment,
    fname varchar(255) NOT NULL,
    lname varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    webUrl varchar(255) NOT NULL,
    comment text NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE mail_list (
    id int(11) NOT NULL auto_increment,
    fname varchar(255) NOT NULL,
    lname varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    webUrl varchar(255) NOT NULL,
    comment text NOT NULL,
    PRIMARY KEY (id)
);

=======================================================================
Application.cfm

<!--- Because we will be making the use of sessions in this program we will need an application file to setup session management. Nothing special about this file as it is as basic as we need for the purposes of this guest book --->

<cfapplication name="guestbook" 
                    sessionmanagement="Yes" 
                    setclientcookies="No" 
                    sessionTimeout="#CreateTimeSpan(0,0,20,0)#">


connection.cfm

<!--- I Make use of this file any time I need to connect to my database, if I move this guest book to a different server this will be the only place I need to change my connection information --->

<CFSET YOUR_DSN = "YOUR_DSN">
<CFSET YOUR_USERNAME = "">
<CFSET YOUR_PASSWORD = "">


<!--- All the way through this application I make use of a common connection template when I need to make a call to the database. This makes it easy to design a program that can be used on any server, as you will only need to make your DSN changes to one file to get the program up and running --->
<cfinclude template="connection.cfm">

<!--- main Query to pull the entries out of the database for display in our guest book template --->
<cfquery name="gbook_output"
             datasource="#YOUR_DSN#"
             username="#YOUR_USERNAME#"
             password="#YOUR_PASSWORD#">

        SELECT   id, 
                     fname, 
                     lname, 
                     email, 
                     webUrl, 
                     comment

        FROM guests

        <!--- Of course we want to show the most recent entries first so we use this Order by statement in our query ---> 
        ORDER BY id DESC 

</cfquery>

<!--- I always stick my output in a table for proper formatting display purposes --->

<table border="1">
    <tr>
        <td align="center">
        <!--- We need to provide the end-user with a link to our signup form --->
        <a href="addentry.cfm">Add Your Comment!</a>
        </td>
    </tr>
    <!--- Now we do our output. It is important to place our cfoutput tag in the proper place so that we can dynamically generate the needed rows for the amount of returns we are displaying. Because we do not know how many rows we need we place the cfoutput tag before the opening <tr> --->
    <cfoutput query="gbook_output">
    <tr>
        <!--- Displaying the entry names is easy but use a <br> tag after the name for good formatting --> 
        <td colspan="2" valign="top">Name: #fname# #lname#<br>
        <!--- Displaying Email addresses and website url uses a cfif tag just in case the user does not want to submit their email address or web url, This cfif statement is actually quite simple all it says is if there is no email address submitted than just show the default text else if there is a email address submitted than show it as a live link. The only part that might confuse anyone is how the link is produced, you will notice that I am basically repeating the database output twice when I am showing the link. The reason is the first echo is for the <a href> tag than the second showing is so we have some clickable text. --->
        Email Address:
        <!--- Cfif No Email display Text --->
        <cfif gbook_output.email eq "">
            No Email Address Submitted!<br>
        <!--- Cfelse show a live mailto link --->
        <cfelse>
            <a href="mailto:#gbook_output.email#">#gbook_output.email#</a> <br>
        </cfif>

        <!--- The same principal is used here for the users website url the only addition to it is adding in a target so that if someone clicks on the website url they will open a new browser window and keep your site window still open --->
        Website Url:
        <!--- Cfif No Url submitted show text --->
        <cfif gbook_output.webUrl eq "">
            No Website Url Submitted!<br>
        <!--- Cfelse show a live Website Url --->
        <cfelse>
            <a href="#gbook_output.webUrl#" target="blank">#gbook_output.webUrl#</a><br>
        </cfif>

        Comments: #gbook_output.comment#
        </td>
    </tr>
    <!--- Note that the closing cfoutput tag is placed after the closing </tr> tag this is part of dynamically generating rows --->
</cfoutput>
</table>


addentry.cfm template

<!--- As your can see there really isn?t anything special about the add entry template just make sure that you set the value of the checkbox to yes! And of course the action of the form tag goes to the template that will process our information and place it into our database! --->

<form action="addentry_action.cfm" method="post" name="addentry_action">
    <table>
        <tr align="center">
            <td><a href="index.cfm">View Guest Book</a></td>
        </tr>
        <tr>
            <td>First Name: <input type="text" name="fname"></td>
        </tr>
        <tr>
            <td>Last Name: <input type="text" name="lname"></td>
        </tr>
        <tr>
            <td>Email Address: <input type="text" name="email"></td>
        </tr>
        <tr>
            <td>Website Url: <input type="text" name="webUrl"></td>
        </tr>
        <tr>
            <td>Comments: <textarea name="comment" cols="50" rows="4"></textarea></td> 
        </tr>
        <tr align="center">
            <td>Add Me To The Mailing List! <input name="mail_list" type="checkbox" value="yes"></td>
        </tr>
        <tr align="center">
            <td><input type="submit" name="Submit" value="Submit"></td> 
        </tr>
    </table>
</form>


addentry_action.cfm

This is where all the fun begins as we are now going to clean, massage and put the contents from the form into our database. Of course we also have to take into account our opt-in mail list checkbox as we are only going to place data into that table if the value has been set to ?Yes? by the end-user.

First off we must create a default structure to hold all of the data in for error checking purposes. In the first part of this template we error check and if there is an error detected we show the end-user the form again but we pull out the data from our structure and place it into the form fields, that way the end-user does not have to re-type their information in again. We also inform them as to which field is causing the problem so that they know which field to edit.

<!--- First we must set a default value for our session or pull a error --->
<cfparam name="session.guest" default="">

<!---Check to see if the structure exists if not create it --->
<cfif NOT IsStruct(session.guest)>
    <cfset session.guest = StructNew()>
</cfif>

<!--- Create default values using the cfparam tag or we could pull errors --->
<cfparam name="session.guest.fname" default="">
<cfparam name="session.guest.lname" default="">
<cfparam name="session.guest.email" default="">
<cfparam name="session.guest.webUrl" default="">
<cfparam name="session.guest.comment" default="">
<cfparam name="Form.mail_list" default="NO">

< !--- If we have values being passed from the form than we clear the structure to make room for the new values --->
<cfset StructClear(session.guest)>

<!--- Now we take the form values and using cfset we place them into a session for later use --->
<cfset session.guest.fname = Trim(Form.fname)>
<cfset session.guest.lname = Trim(Form.lname)>
<cfset session.guest.email = Trim(Form.email)>
<cfset session.guest.webUrl = Trim(Form.webUrl)>
<cfset session.guest.comment = Trim(Form.comment)>
<cfset session.guest.mail_list = Form.mail_list>

<!--- Now that we?ve got our values tucked away in a session we can start our error checking --->

<!--- First we must create a default value for a variable we are naming ?errors? using cfparam again --->
<cfparam name="errors" type="string" default="">

<!---All we are doing here is checking to make sure that the selected fields have at least some value in them other than nothing. There would be nothing worse than having a guestbook full of nothing entries! So using the cfif tag we are saying if the form.fieldname does not have a value show the error text. Note how I have also included the html List tag in the cfif statement. This is because I have no idea how many errors will be generated so I need to dynamically generate a list to show the end-user the errors in a properly formatted list --->
<cfif form.fname EQ "">
    <cfset errors = errors & "<li class=""errors"">You Forgot to enter your first name!</li>">
</cfif>

<cfif form.lname EQ "">
    <cfset errors = errors & "<li class=""errors"">You Forgot to enter your last name!</li>">
</cfif>

<cfif form.comment EQ "">
    <cfset errors = errors & "<li class=""errors"">You Forgot to enter your comments!</li>">
</cfif>

<!--- Using this cfif tag we are wrapping the whole top portion of this template in one giant cfif statement so that this section of the template only shows if there errors are equal more than nothing --->
<cfif errors neq "">

<!--- Note form action is posting back to this same template --->
<form action="addentry_action.cfm" method="post" name="addentry_action">
    <table border="0" cellspacing="0" cellpadding="2">
        <tr> 
            <td>You have some errors in your form submission!
            <!--- This is where we show any errors that we detected with our error checking code placing the errors nicely into a ordered list --->
            <cfoutput> 
            <ol>
                #errors# 
            </ol>
            </cfoutput>
            </td>
        </tr>
        <tr> 
        <!--- Here are our form fields with the original entries posted by our end-user still in them! The values are coming from the session we created in the head of our document, this way now that the user has been informed of which fields contain errors they can just adjust those errors without having to re-enter their data into all the fields over again --->
            <td>First Name:<input type="text" name="fname" value="<cfoutput>#session.guest.fname#</cfoutput>"></td>
        </tr>
        <tr> 
            <td>Last Name:<input type="text" name="lname" value="<cfoutput>#session.guest.lname#</cfoutput>"></td>
        </tr>
        <tr> 
            <td>Email Address:<input type="text" name="email" value="<cfoutput>#session.guest.email#</cfoutput>"></td>
        </tr>
        <tr> 
            <td>Website Url:<input type="text" name="webUrl" value="<cfoutput>#session.guest.webUrl#</cfoutput>"></td>
        </tr>
        <tr> 
            <td>Comments:<textarea name="comment" cols="50" rows="4"><cfoutput>#session.guest.comment#</cfoutput></textarea></td>
        </tr>
        <tr>
            <td>Add Me To The Mailing List!
        <!--- due to the peculiar nature of checkboxs and radio buttons we have to use this system of cfif statements to reflect back the desired value. Not a big deal really but something to keep in mind when working with checkboxs and radio buttons. As a matter of fact you should copy this little snippets somewhere as it will come in handy through many of the applications you build --->
            <cfif session.guest.mail_list eq "yes">
                <input name="mail_list" type="checkbox" value="yes" checked="checked">
            <cfelse>
                <input name="mail_list" type="checkbox" value="yes">
            </cfif> 
            </td>
        </tr>
        <tr> 
            <td><input type="submit" name="Submit" value="Submit"></td>
        </tr>
        <tr> 
            <td><a href="index.cfm">View Guest Book</a></td>
        </tr>
    </table>
</form>

<!--- The cfabort tag is being used to abort the need for the previous code if no error correction is necessary, after it aborts it will carry you to the next section of this template and insert the selected data into its proper places --->
<cfabort>

</cfif>

<!--- THIS IS THE SECOND PART this part only shows if error checking is passed! --->

<!--- We need our database connection again as our abort is ignoring the upper part of this template --->
<cfinclude template="connection.cfm">

<!--- Its always a good idea to trim our data of leading spaces before inserting it into our database so this is what we do now --->
<cfset form.fname = trim(form.fname)>
<cfset form.lname = trim(form.lname)>
<cfset form.email = trim(form.email)>
<cfset form.webUrl = trim(form.webUrl)>
<cfset form.comment = trim(form.comment)>

<!--- Here we are mailing ourselves a copy of the submission, this is a good idea as you can delete possible vulgar posts etc. If you don?t want this function than just comment it out: You can remove it entirely with no consequences to the application, but I would just comment it out as you might change you mind later --->

<!--- Nothing special about this section other than to note that most of the values are coming from the form page so we scope all those values with form --->
<cfmail TO="YOU@YOURADDRESS.COM" 
           FROM="#form.email#" 
           SUBJECT="Guest Book Entry!">

    Information:
    Name: #form.fname# #form.lname#
    Email Address: #form.email#
    Website Url: #form.webUrl#
    Comment: #form.comment# 

</cfmail>

<!--- Now we Insert our data into our database using a cfquery tag --->

<cfquery name="input_entries"
             datasource="#YOUR_DSN#"
             username="#YOUR_USERNAME#"
             password="#YOUR_PASSWORD#">

INSERT INTO guests(
                                fname,
                                lname,
                                email,
                                webUrl,
                                comment
                           )
                  VALUES(
                                '#form.fname#',
                                '#form.lname#',
                                '#form.email#',
                                '#form.webUrl#',
                                '#form.comment#' 
                           ) 
</cfquery>

<!--- Now were going to use cfif to check to see if the value of our mail list checkbox has been set to Yes and if it has been set to yes we will insert the users information into our mailing list table. We are not submitting the users comment into this table as we have no use for them in our mail list table --->

<cfif session.guest.mail_list eq "yes">

<cfquery name="input_entries"
             datasource="#YOUR_DSN#"
             username="#YOUR_USERNAME#"
             password="#YOUR_PASSWORD#">

             INSERT INTO mail_list(
                                            fname,
                                            lname,
                                            email
                                            )
                              VALUES(
                                            '#fname#',
                                            '#lname#',
                                            '#form.email#' 
                               )
</cfquery>

</cfif>

<!--- To finish off this guestbook we should either show the user what he has placed into our guestbook and provide them with a link back to the index template or we could just put a cflocation tag here and force them to anywhere we wish them to go next. --->



The End. I will add-on to this tutorial with Part Two which will be an admin area using the same file sets as this one. So check back and watch for it as it will slip into this application with no problems. After Part Two is finished the complete file set will be available for download from http://www.ideaz.ca/rez_productions/. The download package will be a complete guest book program with all the final formatting that I feel makes for a complete package.

Continue To Part 2 4