How to create a dynamic sitemap for a database-driven website
How to create a dynamic sitemap for a database-driven website

How to create a dynamic sitemap for a database-driven website

This tutorial demonstrates how to generate a dynamic sitemap for a ColdFusion database-driven website, assuming that you retrieve the content of your website from a database and you save the website content according to the concept of "Parent" and "Parent ID".

Let's say that you save your website content (through a content management system or a content publishing tool) in the following database table (name it "content" for example). Example:

ID	PARENT_ID	TITLE	CONTENT	WEIGHT
1	0	Home	Content of this section...	1
2	0	Web Development	Content of this section...	2
3	0	Contact Us	Content of this section...	3
4	2	Macromedia ColdFusion MX	Content of this section...	1
5	2	CSS	Content of this section...	3
6	2	HTML/XHTML	Content of this section...	2
7	4	Tags	Content of this section...	1
8	4	Functions	Content of this section...	2
9	0	About Us	Content of this section...	4

Now, our dynamic sitemap should look like this (assuming that we order the content by WEIGHT) :

Home
Web Development
- Macromedia ColdFusion MX
  - Tags
  - Functions
- HTML/XHTML
- CSS
Contact Us
About Us

We will need to create two ColdFusion pages, "sitemap.cfm" and "getsitemap.cfm" as the following:

getsitemap.cfm

<!---

NOTE: setup your datasource name in your Application.cfm file. example: <cfset application.dsn="datasourcename">

PAGE: getsitemap.cfm

--->

<!--- this part runs when we call the getsitemap.cfm for the first time --->

<cfif not isdefined("request.menuQuery")>

  <cfquery name="request.menuQuery" datasource="#application.dsn#">

	SELECT ID, PARENT_ID, TITLE

	FROM CONTENT

	ORDER BY PARENT_ID, ID

  </cfquery>

  <cfset attributes.parentID="">

<cfelse>

  <cfparam name="attributes.parentID" default="">

</cfif>

<!---

for the first call of getsitemap.cfm, the value of attributes.parentID will be 0 because we didn't pass any argument yet

and the query will retrieve the first level of the content, where PARENT_ID equal to 0 in other words,

the query will return the titles: Home, Macromedia ColdFusion MX, Contact Us, and About Us

--->

<cfquery name="theMenuQuery" dbtype="query">

  SELECT ID, PARENT_ID, TITLE

  FROM request.menuQuery

  WHERE

  <cfif len(attributes.parentID) eq 0>

    PARENT_ID = 0

  <cfelse>

    PARENT_ID=#attributes.parentID#

  </cfif>

  ORDER BY WEIGHT

</cfquery>

<!---

this part displays the titles retrieved by the query theMenuQuery and calls the same page "getsitemap.cfm" but this time we pass

the attribute/argument parentID, in fact the page is being called again for each title to see if it has sub titles and display them

--->

<cfif theMenuQuery.recordCount>

  <ul type="square">

    <cfoutput query="theMenuQuery">

      <li><a href="viewcontent.cfm?id=#id#">#theMenuQuery.title#</a></li>

      <cfmodule template="#getFileFromPath(getCurrentTemplatePath())#" parentID="#theMenuQuery.ID#">

    </cfoutput>

  </ul>

</cfif>

sitemap.cfm

In this page, you just need to include the "getsitemap.cfm" as the following:

<cfinclude template="getsitemap.cfm">

This code helped me with alot of projects, I hope it helps you as well.

All ColdFusion Tutorials By Author: Ikramy Ghidan

Generate a unique number from current date and time
This is a function that generates a 14-digits unique number from the current date and time. You can use this number as a unique ID for your database records.
Author: Ikramy Ghidan
Views: 10,216
Posted Date: Tuesday, May 31, 2005
How to create a dynamic sitemap for a database-driven website
This tutorial demonstrates how to create a dynamic sitemap for a database-driven website with ColdFusion
Author: Ikramy Ghidan
Views: 12,451
Posted Date: Tuesday, May 31, 2005