?
Solved

Database Driven Website Breadcrumb Trail

Posted on 2010-03-23
11
Medium Priority
?
893 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:Kevin Litman
  • 5
  • 4
  • 2
11 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 28372840
That makes sense, in general.   Is the website database driven already?  Every page is in the database?  If not it may be a bit of a hassle to put it all in there just for breadcrumbs.

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.

0
 
LVL 2

Author Comment

by:Kevin Litman
ID: 28373291
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.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 28374496
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.

0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 2

Author Comment

by:Kevin Litman
ID: 28374587
Thats where I am having trouble. Are you able to provide some sample code?
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 28418991
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
<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>&nbsp;&raquo;&nbsp;<cfoutput>#listChangeDelims(lBreadcrumbs, " &raquo; ")# </cfoutput></strong>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 28451831
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...





  <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, " &raquo; ","|")# </cfoutput>
    
    

Open in new window

0
 
LVL 2

Author Comment

by:Kevin Litman
ID: 28470955
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.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 28471605
Since it's a recursive function, it's hard for the function to know where is the start and end of the bread crumbs.  So we have to tell it by adding a parameter.

The second parameter is "showLink" and defaults to False.   Since we don't pass it in when we call it, it won't show for the current page, but when the function calls itself, it always passes a True value so the link will be showed for every parent.


  <cffunction name="getBreadcrumbs" returntype="string" output="false">
      <cfargument name="pageID"   type="numeric" required="true" default="">
      <cfargument name="showLink" required="false" default="1">

      <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,1)>
      </cfif>
      <cfif val(arguments.showLink) and 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, " &raquo; ","|")# </cfoutput>
    

Open in new window

0
 
LVL 2

Author Comment

by:Kevin Litman
ID: 28472240
I had to change the default value of showLink from a 1 to a 0 then it worked perfect.
0
 
LVL 2

Author Closing Comment

by:Kevin Litman
ID: 31706216
Thanks for the help!
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 28503708
Cool gdemaria..

Nice work and it will work as such

Home > Slaes > Departments > Clothings >Show

before show each is linked
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question