Building a Simple ColdFusion Content Management System with 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.

Introduction

These days everyone has a web site or wants one. Unless you want to crack open a book and learn a lot of interesting HTML, you need to get some sort of program or content management system. Content management systems allow you to add and update pages without any other programs to edit the HTML.

The content management system I will describe and walk through will be built in ColdFusion with a MySQL database in the background storing the pages. Along with the ability to add, modify and delete pages, there will also be a self-generating breadcrumb list at the top of the page as well as self-generating menus and a site map. All of this is designed so that there is minimal maintenance, so you can spend more time generating content and less time worrying about coding.

A couple of notes about this system: I will not be adding any sort of browser-based HTML editor in this tutorial. My preference is to code the HTML myself. Just be aware however, that it is easy to add whichever in-line editor you wish. Also, if you are going to use this on the Internet, you will have to tie in a system to verify who you are (Log in and out) so you can have the administrative links show on the page if you are logged in.

{mospagebreak title=Starting With MySQL}

First of all we have to build the table that will hold all our pages. Throughout this tutorial I will use page and database record interchangeably because the pages of this content management system are stored as database records.

CREATE TABLE `tblpages` (
  `id` int(11) NOT NULL auto_increment,
  `parentid` int(11) default NULL,
  `title` varchar(200) default NULL,
  `body` text,
  `createddate` datetime default NULL,
  `updatedate` datetime default NULL,
  `status` char(1) default NULL,
  `hierarchy` varchar(255) default NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
  ) TYPE=MyISAM

I will walk you through what the fields are for and what they will help us to accomplish.

The ID is going to be the unique key for each page. You will be able to call a specific page with the id number.

ParentID is going to allow us to do the site map and breadcrumb trail and also the side menu. This field stores the ID number of the page that is it’s parent. It is hierarchical data in that it represents a parent child relationship. Lets say a page with an id of 3 has a page off of it with an ID of 4. Page 3 would be the parent and page 4 would be the child.

The Title field will be used in several places. First it can be used on the top of every page as (you can probably guess this one) a title. It will also be used in the menu and the breadcrumb list, so when you are entering titles, you don’t want them to be too long.

The Body is for whatever you want to put on the page.

The CreatedDate will store the date and time the page was originally created.

The UpdateDate will get updated every time you modify the page. We will use this field in the site map so that people will readily be able to see how up to date your web site is.

Status is used to delete pages. The value will either be A for active and D for deleted. All the queries to display a page will check to see if the page is active.

The Hierarchy field is build to minimize the server overhead and make the whole page easier to present. All the pages will be organized hierarchically starting with the home page. Instead of running some complex queries every time the page loads, we are going to create a list of the hierarchy when the page is created. We will use the page ID. Once created the Hierarchy will look something like this “1~3~5~12″. What that means is that the page with ID equal to 1 (the home page) has a child page off of it with an ID of 3, which has a child page off of it with an ID of 5, which has a child page off of it with an ID of 12.

{mospagebreak title=The Page and how to Update/Modify and Delete}

We will now start writing some code to display a page and also to modify add and delete pages. I am going to write the code so that you can put it within the body tags and the entire piece is going to be located on the same page. Let’s start out with the CFIF statement that is going to control the whole page. We are also going to set a variable that will specify your data source.

<cfset data = “YourDataSource”>
<cfif isdefined(“url.mod”) or isdefined(“url.add”)>
<cfelseif isdefined(“form.modify”)>
<cfelseif isdefined(“url.delete”)>
<cfelseif isdefined(“form.add”)>
<cfelse>
</cfif>

So this is the flow of the program. If nothing is specified it is just going to load the page (the CFELSE). But if you are going to add, modify or delete, it will look at that specific area. The way we are going to create the hierarchy is that when you are adding a page, you have to select the add link from the page you want to create the child page off of. For example if you had a page on “building a bird house” and you wanted to create a child page off of it for materials, you would have to go to the “build a bird house” page then click on the add link. What this does is it provides the form with the ID of the parent page so that you can insert it into the PARENTID field.

Show Page

So let’s build the CFELSE part (view the page). The first part will be the CFQUERY that will query out the record you are requesting (url.id). The below code is going to set a CFPARAM so that if the page is requested without a URL.ID it will load the home page.

 <cfparam name=”url.id” default=”1″>
  
 <cfquery name=”getpage” datasource=”#variables.data#”>
  Select *
  From tblpages
  where id = #url.id# and status = ‘A’
 </cfquery>
 
 <cfoutput>  
   #getpage.body# 
 </cfoutput>

 <!—  Page/Site administration  —> 
<div align=”center”>
  <!— put an if statement around this cfoutput to see if
   you are logged in. —>  
  <cfoutput>
   <a href=”page.cfm?mod=#getpage.id#” target=”_self”>Modify</a> |   
   <a href=”page.cfm?add=#getpage.id#” target=”_self”>Add</a> |    <a href=”page.cfm?del=#getpage.id#” target=”_self” onclick=”return
    confirm(‘Do you really want to Delete this?’)”>Delete</a>
  </cfoutput> 
</div>

The only variable you are outputting is the BODY field from the query. This is a Text field that holds all the words and HTML that makes up your page. The next section is for page/site administration. There will be three links at the bottom of the page.

The Modify link is to modify the existing page you are on.
The Add page is to add a child page off of the current page.
The Delete link is to delete the current page.

{mospagebreak title=Add/Modify Form}

We are now going to look at the add and modify process of this application. When you click on either the Modify or Add link it will take you to the first part of the CFIF statement. I am just going to state once for the record that these forms were intentionally left very plain. I was going for function, you can make it look nice when you integrate it into your site.

Here is the code:

1  <cfif isdefined(“url.mod”)>
           <cfset url.id = #url.mod#>
   <cfelse>
           <cfset url.id = 0>
   </cfif>
2  <cfquery name=”getpage” datasource=”#variables.data#”>
3     Select *
4     From tblpages
5     where id = #url.id# and status = ‘A’
6  </cfquery>
7  <form action=”page.cfm” method=”post” name=”form1″>
8  <cfoutput>
9  Title:<br />
10 <input type=”Text” name=”title” value=”#getpage.title#” /><br />
11
12 Body:<br />
13 <textarea name=”body” cols=”40″ rows=”10″>#getpage.body#</textarea>
14 <br />
15
16 <cfif isdefined(“url.add”)>
17   <input type=”Hidden” name=”id” value=”#url.add#” />
18   <input type=”Submit” name=”Add” Value=”Add” />
19 <cfelse>
20   <input type=”Hidden” name=”id” value=”#url.mod#” />
21   <input type=”Submit” name=”Modify” value=”Modify” />
22 </cfif>
23 </cfoutput>
24 </form>
 

Now I will walk through the above code. The first CFIF statement (line 1) sets a url.id to either 0 for a new record, or a value that was passed in the “url.mod”. What this does for you is if you are modifying a page, it will show the information in the form, if you are adding a page the form will be blank.

The CFQUERY queries out the record if you are modifying. Set the form’s ACTION to the same page you are working on. Lines 8 through 14 place the form fields on the page and populate them with values if you are modifying the record. Lines 16 through 23 test to see what you are trying to and sends you to the appropriate action. If you are trying to modify an existing document the submit button is named Modify with a value of Modify. If you are trying to add a new document then the submit button is named Add with a value of Add. That way you can put the appropriate action in the section of the page’s CFIF statements. All you have to do is see if the form variables are ISDEFINED.

{mospagebreak title=Action}

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”>

{mospagebreak title=Breadcrumb List}

Breadcrumb lists are useful for many people that may be navigating your site. As they click deeper in your site, they often would like to go back up the hierarchy of it or simply go back to the home page. We are going to create a new page (you can call it whatever you want but for this tutorial I will name if Breadcrumb.cfm) for the breadcrumb code, then CFINCLUDE it into the top of the page after the CFQUERY for the page. So on a new ColdFusion template add the following.

1   <!— Bread Crumb —>
2   <cfset breadcrumblist = “”>
3   <cfloop list=”#getpage.hierarchy#” delimiters=”~” index=”i”>
4     <cfquery name=”breadcrumb2″ datasource=”#variables.data#”>
5       select *
6       From tblpages
7       Where id = #i# and status=’A’
8    </cfquery>
9    <cfset breadcrumblist = #breadcrumblist# &
10  ‘ &gt; <a href=”page.cfm?id=#breadcrumb2.id#” target=”_self”>
11  #breadcrumb2.title#</a>’>
12  </cfloop>
13
14  <cfif listlen(breadcrumb.hierarchy, “~”) gt 1>
15   <cfset baseid = #listgetat(breadcrumb.hierarchy, 2, “~”)#>
16  <cfelse>
17   <cfset baseid = 1>
18  </cfif>
19  
20  <cfoutput>#variables.breadcrumblist#</cfoutput>
21  <!— Bread Crumb —>

Now let’s walk through it. Line 2 sets a variable that will contain the list. Line 3 starts the CFLOOP that will loop through the HIERARCHY field that we have built on the record add action previously discussed. Since we already ran the CFQUERY for the current page, we can use the results in the CFLOOP. We are going to loop through the hierarchy list to query out the title of all the pages that will be shown on the breadcrumb list. Lines 4 through 8 query the records every time through the loop using the value in the hierarchy list to query out the ID of the record. Lines 9 through 11 add to the breadcrumb list variable with all the HTML markup so people can click on the link to bring them to the page.

Lines 14 through 18 set a variable called “Baseid”. This variable will be used for the menu that will be explained in the next section. The menus I will be building next will be based on the HIERARCHY field. The menu may get rather long if it displays all of the pages within the site (if the site you are building is going to get large). What I am trying to accomplish with the Baseid variable is to determine what section of the site you are at. I am trying to get the ID of the second level page, which would be pages that are the children of the home page. Line 14 starts a CFIF statement that checks to see if the hierarchy list is greater than 1 (not the home page). If the CFIF is true, then it CFSET’s the baseid variable to the second number in the hierarchy list. Line 16 and 17 catch the false value and CFSETS the baseid to 1 because it is the homepage. Line 20 simply outputs the breadcrumb list on the page.

{mospagebreak title=Side Menu}

As I stated above, this menu is designed to show up on the side of the page, and that it will expand if you are within a subsection of the home page. For example if your web site had several major sections, Carpentry and Stamp Collecting. If someone clicked on Stamp Collecting, all of the child pages would expand (similar to browsing for files, you click on a folder and the sub folders become visible).

Let look at the code. You can either include this in the menu section of your site or create a new page with this code and CFINCLUDE it in.

1  <cfparam name=”baseid” default=”1″>
2  <cfquery name=”menusub” datasource=”#variables.data#”>
3    select *
4    From tblpages
5    Where status=’A’
6    Order by hierarchy
7  </cfquery>
8  <cfoutput query=”menusub”>
9   <cfif listlen(menusub.hierarchy, “~”) gt 1>
10   <cfif listgetat(menusub.hierarchy, 2, “~”) eq baseid>
11     <cfloop from=”2″ to=”#listlen(menusub.hierarchy, “~”)#” index=”i”>
12  –
13   </cfloop>
14    <a target=”_self” href=”page.cfm?id=#menusub.id#” 
15    title=”#title#”>#title#</a><br>
16   <cfelseif level eq 2> <!— NON EXPANDED MENU —>
17    – <a target=”_self” href=”page.cfm?id=#menusub.id#”
18   title=”#title#”>#title#</a><br>
19   </cfif>
20  <cfelse> <!— HOME —>
21  <a target=”_self” href=”page.cfm?id=#menusub.id#”
22  title=”#title#”>#title#</a><br>
23  </cfif>
24 </cfoutput>

So now let’s walk through the above section of code. I create a CFPARAM for the baseid variable and set its default to 1. This is so you can run pages that you may create that do not run off of this content management system. Due to the fact that it does not run off the content management system it will not have a baseid. The CFPARAM keeps it from throwing an error.

The CFQUERY on lines 2 through 7 queries out all of your active records and order them by the HIERARCHY field. Line 8 starts the CFOUTPUT of the menu listing. Line 9 starts a CFIF that checks the length of the hierarchy list. If the length is 1 then it is the home page which is dealt with on lines 20 to 23. Line 10 gets back to a variable we created while making the breadcrumb list, the Baseid. We use this variable to check to see if the child off of the homepage is part of the hierarchy you are looking at. If so we are going to expand all the child links from the page with the Baseid. All of the other pages that have a hierarchy level 2 that are not the baseid, will not expand their child pages . If you did not want this to happen you could take out the CFIF and its parts out of line 10. Lines 11 through 13 are just for decoration. That section loops through the hierarchy list length and shows the dashes accordingly. In the CFLOOP I started with 2 in the FROM attribute so that the child pages of the home page would not have a dash in front of them. You could put anything you want in this loop, even spaces. Lines 14 through 15 build the link by using the title and the ID of the record. Lines 16 through 19 finish off the CFIF statement by showing the child pages off of the home page without expanding them. Lines 20 to the end simply display the link for the home page. For this one, since you know exactly what the page is going to be, instead of querying out the title, you could manually code the name (e.g. your site name or use an image).

That is it for the side menu, the breadcrumb list and also the page itself. As an added bonus however, I am going to throw in a self building site map.

{mospagebreak title=Site Map}

Site maps are useful to show people your entire site at a glance so they can more easily find what they are looking for. Another added bonus of a site map is it helps search engines spider your site. Here is the code.

1   <cfquery name=”sitemap” datasource=”#variables.data#”>
2    select *
3    From tblpages
4    Where status=’A’
5    Order by hierarchy
6   </cfquery>
7    <cfoutput query=”sitemap”>
8    <cfloop from=”2″
9  to=”#listlen(menusub.hierarchy, “~”)#”
10  index=”i”> – </cfloop>
11  <a target=”_self” href=”page.cfm?id=#menusub.id#”
12   title=”#title#”>#title#</a>
13    : (#dateformat(updatedate, “M/D/YYYY”)#)<br>
14 </cfoutput>

Start by creating a new page called Sitemap.cfm or whatever you want to call it. Place the above code on it. What this will give you is your entire web site laid out in a hierarchical format with the last date the page was updated. Lets walk through the code quickly. Most of it should look familiar, because it is a very stripped down version of the side menu. This one includes all of the parents and children. The query is performed on all the active records and CFOUTPUT on line 7. I am using the same CFLOOP to put the dashes before the links. Lines 11 through 13 put the page links and the revision date on the page. That is it. Very simple because you have already done all the work on the add a new page action (populated the hierarchy variable).

Conclusion

We have walked through the creation of a simple but powerful content management system. We have leveraged the power of building the pages with a hierarchical structure which allows us to dynamically build out a breadcrumb list, a side menu and also a site map page. This was done to minimize the amount of time it takes to maintain and update a web site. This was not meant to be an extensive build out of a content management system, but to provide you with the core of what is needed. In order to put this on a public server, you still have to provide a way to authenticate the administrator so that you can lock all of the administrative functions from the public.

[gp-comments width="770" linklove="off" ]

chat