Kevin Litman
asked on
Database Driven Website Breadcrumb Trail
I am using ColdFusion 9 and SQL2005. I am trying to come up with a breadcrumb trail I can place at the top of the page. Example... Home > Departments > Building. Each level would be a link to that page of the site. I want this to be database driven and not based off of the structure of the site. In my database I currently have PageID, ParentID, Title, Link. I'm not sure if this is the best way but what I was thinking was that every page would have a record in the database and be identified by PageID and if it had a parent page that field would have the parents PageID. Title and Link would be used for the link in the trail. I attached an image of a small example of the database I currently have. Any help would be greatly appreciated.
Untitled-2.jpg
Untitled-2.jpg
ASKER
Everything is in a database already. The reason I dont want to use the directory structure is because I have a few subdomains in the site. So Home > Departments > Building which can be accessed by going to http://mydomain.com/departments/building has a subdomain of http://building.mydomain.com. If I use the directory structure approach pages in building.mydomain.com think the parent is building.mydomain.com instead of mydomain.com so my trail would be Home instead of Home > Departments > Building.
Since the pages are already in the database, that's the smoothest way anyway.
So all you need to do is determine what page you're on, fetch the relevant records from the table using the parent ID of each one and then cfloop over them to display the links.
So all you need to do is determine what page you're on, fetch the relevant records from the table using the parent ID of each one and then cfloop over them to display the links.
ASKER
Thats where I am having trouble. Are you able to provide some sample code?
Here you go, Change the values and you will get the working example with your code:
the code expects that you pass the url.pic passed
ok
the code expects that you pass the url.pic passed
ok
<cffunction name="getBreadcrumbs" returntype="string" output="false">
<cfargument name="id" type="numeric" required="true" default="#url.pic#">
<cfset var qThis = "">
<cfset var lReturn = "">
<cfquery name="qThis" datasource="#request.dsn#" maxrows="1">
select * from images
inner join categories on categories.pic_ID = images.pic_cid
where images.pic_id = <cfqueryparam value="#arguments.id#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfif len(qThis.pic_pid[1])>
<cfset lReturn = getBreadcrumbs(id=qThis.pic_pid[1])>
</cfif>
<cfreturn listAppend(lReturn, "#qThis.pic_cat_name[1]#")>
</cffunction>
<cfset lBreadcrumbs = getBreadcrumbs(id=#url.pic#)>
<strong><a href="index.cfm">Home</a> » <cfoutput>#listChangeDelims(lBreadcrumbs, " » ")# </cfoutput></strong>
That's a nice recursive function.
The only thing is that it doesn't take the links into account.
Here's the code adjusted for your database table (assuming your table is called PAGES)
This adds the link...
The only thing is that it doesn't take the links into account.
Here's the code adjusted for your database table (assuming your table is called PAGES)
This adds the link...
<cffunction name="getBreadcrumbs" returntype="string" output="false">
<cfargument name="pageID" type="numeric" required="true" default="">
<cfset var pageQry = "">
<cfset var thisLine = "">
<cfset var fullString = "<a href='/'>Home</a>">
<cfquery name="pageQry" datasource="#request.dsn#" maxrows="1">
select parentID, title, link
from pages
where pageID = <cfqueryparam value="#val(arguments.pageID)#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfif val(pageQry.parentID)>
<cfset fullString = getBreadcrumbs(pageQry.parentID)>
</cfif>
<cfif len(pageQry.link)>
<cfset thisLine = "<a href='#pageQry.link#'>#pageQry.title#</a>">
<cfelse>
<cfset thisLine = pageQry.title>
</cfif>
<cfreturn listAppend(fullString, thisLine, "|")>
</cffunction>
<cfset lBreadcrumbs = getBreadcrumbs(currentPageID)>
<cfoutput>#listChangeDelims(lBreadcrumbs, " » ","|")# </cfoutput>
ASKER
gdemaria I like this solution a lot. How could I go about making the last item in the trail not linked? So lets say I have Home >> Departments >> Building. Home and Departments would be linked but Building would not because I am currently on that page.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to change the default value of showLink from a 1 to a 0 then it worked perfect.
ASKER
Thanks for the help!
Cool gdemaria..
Nice work and it will work as such
Home > Slaes > Departments > Clothings >Show
before show each is linked
Nice work and it will work as such
Home > Slaes > Departments > Clothings >Show
before show each is linked
Another approach could be using the directory hierarchy. If you are in HR under administration under departments, that's all you need to know. You just need to look up the "friendly" name for each one of those directories. This can be done in the database or by a simple stored structure.