|
|
Posted by Geoff Muldoon on 02/02/07 05:14
laurente1234@yahoo.com says...
> I think it is not a good table design. You need to learn database
> normalization first.
> http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
Internally self-referencing tables are actually *good* database design,
and satisfy normalization rules.
<aside>
Because it's difficult to follow context.
Why is top-posting bad?
</aside>
> > Here is the data in the category table
> > 1, England, 0
> > 2, West Yorkshire, 1
> > 3, Batley, 2
> > 4, Leeds, 2
> > 5, Bradford, 2
> >
> > What I want to display when I am browsing through sub-categories of links is
> > not only the name of that sub-category but it's category's parents, grand
> > parents and so on like the below example.
> >
> > UK >> West Yorkshire >> Batley
> >
> > I see this on a lot of directory sites but none of my PHP books cover how
> > this is done. Must be quite simple so can someone please point me in the
> > right direction. Hope I've explained it well enough, haven't a clue what
> > this process is called.
It's most commonly called "breadcrumbing" (from the Hansel & Gretel fairy
tale).
The following pseudo-code should do the job, but is *not* optimised as I
nowadays use Oracle which supports the wonderful CONNECT BY construct to
recursively query self-referencing tables, so this is a simple
slegdehammer MySQL version.
My MySQL (and some of my PHP) is *very* rusty, but if there are any
errors, at least it will give you an idea of how it might be done:
Note also that it doesn't cater for bad data, eg. a link_id which won't
resolve back to an eventual parent with a category_id of 0.
<?php
function makeBreadcrumbs($link_id) {
// set up a counter
$counter=1;
// get the name for this link_id
$query="SELECT link_name, category_id FROM link WHERE link_id=$link_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// put it as the first row in an array
$bread_crumb_array=array();
$bread_crumb_array[$count]=$row("link_name");
// get the parent
$category_id=$row("category_id");
// now climb the tree if not already at the top
while($category_id!=0) { // careful of errors here !!!
// this time get the parent's name
$query="SELECT link_name, category_id FROM link WHERE link_id=
$category_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// increment the counter
$counter++;
// put it in the array
$bread_crumb_array[$counter]=$row("link_name");
// get the next parent
$category_id=$row("category_id");
}
// now you have an array of names to the top in reverse number order
// get the last one first
$breadCrumbString=$bread_crumb_array[$counter];
// work back through the array
while($counter>0) {
$breadCrumbString.=" >> ".$bread_crumb_array[$counter];
// decrement the counter
$counter--;
}
return $breadCrumbString;
}
// test using the example
$link_id=3;
echo makeBreadcrumbs(link_id);
?>
Geoff M
[Back to original message]
|