This was good then... but is there a better way now? Scott Offord
By: Ikramy Ghidan
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
- Macromedia ColdFusion MX
- 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")>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 --->
<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">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
--->
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>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
--->
<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.
About This Tutorial
Author: Ikramy Ghidan
Total Views: 29,474
Submission Date: May 31, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Skill Level: Advanced
Platforms Tested: CF5,CFMX,CFMX7Total Views: 29,474
Submission Date: May 31, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Discuss This Tutorial
-
-
You should add weight to the select statement, e.g. SQL = "SELECT ID, PARENT_ID, TITLE, WEIGHT FROM request.menuQuery WHERE PARENT_ID = 0 ORDER BY WEIGHT"
-
Hi, I've got it working, but I only got an query error: ---------- Error Diagnostic Information Query Manipulation Error Code = 0 WEIGHT is not a column that can be sorted on. SQL = "SELECT ID, PARENT_ID, TITLE FROM request.menuQuery WHERE PARENT_ID = 0 ORDER BY WEIGHT" ----------- If I comment the "order by" out, then it's working fine. But actually I want the order function. Any help/suggestion? Nils
-
Thanks. Scott Offord.
-
this doesn't make much sense. i wish these tutorials had comments after every line explaining each variable and the purpose of each line. i would like to have a menu system for a cms but its hard wrapping my head around the logic behind it. why are you prefixing those variables with attributes. anyway?
Advertisement
