Home arrow MySQL arrow Page 5 - Building a Simple ColdFusion Content Management System with MySQL

Action - MySQL

This tutorial lays out the basics for creating a content management system in ColdFusion and MySQL. The System leverages hierarchical data to automatically build breadcrumb lists, a side navigation menu, as well as a site map. The goal of this tutorial is to provide a framework upon which to build a content management system that is easy to customize and maintain, leaving the web author more time to create content than maintain code.

  1. Building a Simple ColdFusion Content Management System with MySQL
  2. Starting With MySQL
  3. The Page and how to Update/Modify and Delete
  4. Add/Modify Form
  5. Action
  6. Breadcrumb List
  7. Side Menu
  8. Site Map
By: Charles Kaufmann
Rating: starstarstarstarstar / 48
August 11, 2004

print this article



I will start out with the easiest actions, which are the modify and delete. Let's work on the Modify action first. The only thing that you have to do here is a simple update query as shown below. One thing that I will do before to make both the add, delete and modify query easier to write is to CFSET a variable to the current date and time. I am going to add this before the start of the entire page's CFIF statements.

 <cfset currentdatetime = '#dateformat(now(), "YYYY-MM-DD")#
   #timeformat(now(), "HH:MM:SS")#'>Now back to the modify action.

 <cfquery name="getpage" datasource="#variables.data#">
  Update tblpages
  Set  title = '#form.title#',
       body = '#form.body#',
       updatedate = '#variables.currentdatetime#'
   where id = #form.id#
 <cflocation url="page.cfm?id=#form.id#">

The only fields you are updating are the TITLE the BODY and the UPDATEDATE. In the Add/Modify form you are passing the page/record ID to the action page in a hidden field, so we are going to use that ID in the CFLOCATION to go back to the page you just modified to see what it looks like. That's it for the modify section.

The Delete action is very similar to the Modify action but you only need to update two field, the STATUS and UPDATEDATE fields.

 <cfquery name="delete" datasource="#variables.data#">
  Update tblpages
  Set  status = 'D',
  updatedate = '#variables.currentdatetime#'
  where id = #url.delete#
 <cflocation url="page.cfm?id=1">

That is it for the delete query. All you really need is the change of STATUS from A to D. The letter you use to indicate the page has been deleted doesn't really matter, you can chose whatever you would like. I also added the UPDATEDATE, so you could find out when the page was deleted if needed. The CFLOCATION takes the user back to the web site home page. Now to the Add a new page section.

The Add a new page action does the most because it is figuring out the hierarchy of the page and writing it to the field. The first part of this is an Insert query. Since we are adding a new page, we want to insert the CREATEDDATE as well as a UPDATEDATE. The second query grabs the ID of the record you just inserted in the first query.

 <cfquery name="getpage" datasource="#variables.data#">
  Insert into tblpages
   (parentid, title, body, createddate, updatedate, status)
   (#form.id#, '#form.title#', '#form.body#',
   '#variables.currentdatetime#', 'A')
 <cfquery name="getnewpage" datasource="#variables.data#">
    select max(id) as newid
    from tblpages

The next two steps we have to work on are to get the hierarchy of the page and to insert that information into the record. First we will CFSET a variable for the list we are going to create. This is shown below.

  <cfset hierarchylist = '#getnewpage.newid#'>

Now that we have that out of the way, we are going to loop through the records to add to that list all of the parentid's that we need. The first thing you will notice is that the whole procedure is surrounded by a CFIF statement that tests to see if the form.id of the record is 0, which if it is, that would be the home page of the web site (id = 1 and parentid = 0). And if it is the homepage, the only number you need in the hierarchylist is "1" which has already been set in the CFSET. Line 2 sets a variable to the parentid of the record you just inserted. The variable will be reset within the loop. Line 3 sets up the attributes of the CFLOOP. We are going to use a conditional loop which will keep testing to make sure the latest parentid is not 0, because if it is, you have reached the home page and you need to terminate the loop. Lines 4 through 8 query the database with the new parent variable that is reset every time the CFLOOP occurs. So what this does is it goes up the hierarchy from child to parent. Line 9 adds the new record ID to the front of the hierarchylist (listprepend). Line 10 set the "newparent" variable to the new "parentid" so that it is prepared for another CFLOOP. The rest is just closing tags.

1 <cfif form.id gt 0> 
2  <cfset newparent = #form.id#> 
3  <cfloop condition="newparent Greater Than 0"> 
4  <cfquery name="hierarchylist2" datasource="#variables.data#"> 
5   select * 
6   From tblpages 
7   Where id = #newparent# and status='A' 
8  </cfquery> 
9  <cfset hierarchylist = #listprepend(hierarchylist, hierarchylist2.id, "~")#> 
10  <cfset newparent = #hierarchylist2.parentid#> 
11  </cfloop> 
12 </cfif>

So now we have the "hierarchylist" list created, now we have to insert it into the database. We will do this with an update query. After that is accomplished we do a CFLOCATION to the new page you just added.

<cfquery name="getpage" datasource="#variables.data#">   
     Update tblpages   
     Set hierarchy = '#hierarchylist#'       
     where id = #getnewpage.newid#  
<cflocation url="page.cfm?id=#getnewpage.newid#" addtoken="no">

>>> More MySQL Articles          >>> More By Charles Kaufmann

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: