Action - MySQL

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#
 </cfquery>
 <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#
 </cfquery>
 <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)
  Values
   (#form.id#, '#form.title#', '#form.body#',
   '#variables.currentdatetime#',
   '#variables.currentdatetime#', 'A')
 </cfquery>
 <cfquery name="getnewpage" datasource="#variables.data#">
    select max(id) as newid
    from tblpages
 </cfquery>

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#  
</cfquery>
<cflocation url="page.cfm?id=#getnewpage.newid#" addtoken="no">