PHP Breadcrumbs - most efficient way.

Hi, I'm writing a basic breadcrumb script and it works fine but its neither efficient (bad database design, which I'm about rectify) or robust. So I was wondering if someone had a way to do this with as minimal queries as possible.

The point is I have many pages stored in the database and they all have different parents. Theoretically it should be able to handle unlimited levels though in practice it will rarely exceed 5 levels deep.

So my database schema looks like this:

id     |  name  | parent
1      |  josh  | wayne
2      |  jess  | josh
3      | lauren | jess
4      | wayne | 

Code (markup):

So simple enough to understand. So if you are visiting Lauren's page the breadcrumbs will be Wayne > Josh > Jess > Lauren.

Easy enough to understand, easy enough to execute as well but when you start dealing with great-grandparents the way I'm doing (which calls multiple queries) is hopelessly inefficient.

Would love to see what you can come up with

Thanks

 

Would probably help to show my code. It is ripped from a bigger script, so I'll try to make it make sense:

 
//$db and $query are PDO objects and methods.
//$parent = The immediate parent of the current page, generated when the page is loaded.
 
function getBreadcrumbs($thisPage){
  global $db, $query;
  $breadcrumbList = '<hr/>';
 
  $query->execute(array($thisPage));
  $data = $query->fetchAll();
  $parent = $data[0]['parent'];
 
  //So yea if the parent is not the root, keep finding parents of that parent.
  if(!empty($parent)){
      $pParent = getParent($parent); //getParent() runs a query every time. Horrible
      $crumbs = array();
      while(!empty($pParent)){
          $crumbs[] = $pParent;
 
          $pParent = getParent($pParent);
   
      }
   
      $crumbs = array_reverse($crumbs); //Gets everything in the right order.
   
    #As you can see I have to run a while loop and a foreach because I have to sort
    #array. That really annoys me and I'm sure there's a more efficient way of doing it.
      foreach($crumbs as $crumb){
          $breadcrumbList .= '<a href="?page='.$crumb.'">'.$crumb.'</a> &gt; ';
      }
   
      $breadcrumbList .= '<a href="?page='.$parent.'">'.$parent.'</a> &gt; '. $thisPage;
  }
 
  $breadcrumbList = ($breadcrumbList == '<hr/>') ? '' :$breadcrumbList;
 
  return $breadcrumbList;
}
 

PHP:

 

You can't do this with simple SQL, you will need to get into stored procedure to do recursive logic which is my first choice.

For simplistic reasons, let's just do it with simple query. First make a query to get the list of all pages/parents and store them in the following format:

$array = array (
  'josh'    => 'wayne',
  'jess'    => 'josh',
  'lauren' => 'jess',
  'wayne'    => NULL
);

Code (markup):

You then get the breadcrums you need (passing the above array from query and page you want the breadcrumbs too). It is not complete but you should be easily above to modify it to add the name & arrow.

function getBreadCrumbs($array, $page)
{
    // template (easy change)
    $template = '<a href="?page=%page%">%page%</a>';
   
    // set pages
    $pages = array(str_replace('%page%', $page, $template));
    $parent = $array[$page];
 
    // Do parents
    while ($parent != NULL) {
        array_unshift($pages, str_replace('%page%', $parent, $template));
        $parent = $array[$parent];
    }
   
    return $pages;
}

Code (markup):

 

Thanks, but not really feasible for large amounts of data - recursive logic suits me fine - I'm not a complete novice, just a bit out of practice and a lot tired That said advanced SQL is not my thing. I'll look through those and hopefully throw something together tonight or tomorrow - thanks for that link.

The array thing would work but is not so efficient as the actual family tree is just an example - there are actually over 90000 rows. Just trying to keep things as efficient as possible so 1 SQL query would be ideal.

It looks like it's going to take a lot to understand as everybody I see, seem to be doing multiple tables and joining them, though I've found a couple of leads, I hope I'll find something I can make workable.

 

Oh 90K is too much. Maybe you can share your final procedure for others who might run into the same issue if you are ok with it.