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

Starting With MySQL - 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



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`)

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.

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