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
LVL 2
Kevin LitmanNetwork AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
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
Kevin LitmanNetwork AdministratorAuthor Commented:
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
gdemariaCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Kevin LitmanNetwork AdministratorAuthor Commented:
Thats where I am having trouble. Are you able to provide some sample code?
0
Gurpreet Singh RandhawaCEOCommented:
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
gdemariaCommented:
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
Kevin LitmanNetwork AdministratorAuthor Commented:
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
gdemariaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin LitmanNetwork AdministratorAuthor Commented:
I had to change the default value of showLink from a 1 to a 0 then it worked perfect.
0
Kevin LitmanNetwork AdministratorAuthor Commented:
Thanks for the help!
0
Gurpreet Singh RandhawaCEOCommented:
Cool gdemaria..

Nice work and it will work as such

Home > Slaes > Departments > Clothings >Show

before show each is linked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.