Breadcrumbs...help!

[James]

Got a table of with an ID column and a ParentID column. The ParentID column
of a record points to the ID of another record. They can form chains
several records deep.

Is there any particularly good way to retrieve a table containing a list of
crumbs in the breadcrumb trail, in sequence?

If at all possible, I'd like to do it with a single database query. I know
this probably isn't feasible, but I'd love it if anyone happens to know a
way to do it!

Thanks,
James

[James]

Re: Breadcrumbs...h elp!

If it helps at all, here's an example the array I'm working with. I'd like
to try to to do it using just the array (i.e., without additional db calls).
An example breadcrumb trail might be:

Home > Food > Fruits > Banana

Here's the array:

Array
(
[0] => Array
(
[ID] => 7
[UserID] => 1
[ParentID] => 0
[Title] => Food
)

[1] => Array
(
[ID] => 2
[UserID] => 1
[ParentID] => 0
[Title] => Test
)

[2] => Array
(
[ID] => 6
[UserID] => 1
[ParentID] => 0
[Title] => Test
)

[3] => Array
(
[ID] => 5
[UserID] => 1
[ParentID] => 0
[Title] => Test
)

[4] => Array
(
[ID] => 8
[UserID] => 1
[ParentID] => 7
[Title] => Fruits
)

[5] => Array
(
[ID] => 9
[UserID] => 1
[ParentID] => 7
[Title] => Vegetables
)

[6] => Array
(
[ID] => 10
[UserID] => 1
[ParentID] => 8
[Title] => Banana
)

[7] => Array
(
[ID] => 11
[UserID] => 1
[ParentID] => 9
[Title] => Test
)

)






"James" <james@nowhere. com> wrote in message
news:bGuzc.5470 $US1.623@fed1re ad02...[color=blue]
> Got a table of with an ID column and a ParentID column. The ParentID[/color]
column[color=blue]
> of a record points to the ID of another record. They can form chains
> several records deep.
>
> Is there any particularly good way to retrieve a table containing a list[/color]
of[color=blue]
> crumbs in the breadcrumb trail, in sequence?
>
> If at all possible, I'd like to do it with a single database query. I[/color]
know[color=blue]
> this probably isn't feasible, but I'd love it if anyone happens to know a
> way to do it!
>
> Thanks,
> James
>
>[/color]

[Nikolai Chuvakhin]

Re: Breadcrumbs...h elp!

"James" <james@nowhere. com> wrote in message
news:<bGuzc.547 0$US1.623@fed1r ead02>...[color=blue]
>
> Got a table of with an ID column and a ParentID column. The ParentID column
> of a record points to the ID of another record. They can form chains
> several records deep.
>
> Is there any particularly good way to retrieve a table containing a list of
> crumbs in the breadcrumb trail, in sequence?[/color]

Try a different data design. For example:

Table `posterity`
ID (unique, indexed)
description

Table `ansectors`
ID (non-unique, indexed)
generation
ancestor (non-unique, indexed)

Now, let's say we have record #2, which has no parent and is
a parent to record #14, which is a parent to record #25, which
is a parent to record #47, which is a parent to record #79.

So here's what table `ancestors` would look like:

ID generation ancestor
2 0 NULL
14 1 2
25 1 2
25 2 14
47 1 2
47 2 14
47 3 25
79 1 2
79 2 14
79 3 25
79 4 47

In other words, in the `ancestors` table we record the entire
"genealogy" of each record from the `posterity` table. The
downside is that we have to generate and keep a lot of redundant
data. The upside is that searching for ancestors becomes simple;
we can do something like this:

SELECT generation, ancestor
FROM ancestors
WHERE ID = 79
ORDER BY generation;

and get something like this in return:

+-------------+-----------+
| generation | ancestor |
+-------------+-----------+
| 1 | 2 |
| 2 | 14 |
| 3 | 25 |
| 4 | 47 |
+-------------+-----------+

Another possibility is to stick with the existing data design,
but add a text column (let's call it `Lineage`) and keep the
entire genealogy there like this: 47-25-14-2. Then you can
retrieve this column, explode() it by '-', and thus find ID
numbers for all ancestors lined up by generation in descending
order.

In either case, you can say with certainty that record #79 is
a fifth-generation entity descending from records #47, #25,
#14, and, ultimately, #2.

Cheers,
NC

[Chung Leong]

Re: Breadcrumbs...h elp!


"James" <james@nowhere. com> wrote in message
news:bGuzc.5470 $US1.623@fed1re ad02...[color=blue]
> Got a table of with an ID column and a ParentID column. The ParentID[/color]
column[color=blue]
> of a record points to the ID of another record. They can form chains
> several records deep.
>
> Is there any particularly good way to retrieve a table containing a list[/color]
of[color=blue]
> crumbs in the breadcrumb trail, in sequence?
>
> If at all possible, I'd like to do it with a single database query. I[/color]
know[color=blue]
> this probably isn't feasible, but I'd love it if anyone happens to know a
> way to do it!
>
> Thanks,
> James
>
>[/color]

Well, provided that that's a limit to how depth the structure can go, you
can fetch data in a single query by doing multiple left joints:

SELECT * FROM cow a
LEFT JOIN cow b ON a.ParentID = b.ID
LEFT JOIN cow c ON b.ParentID = c.ID
LEFT JOIN cow d ON c.ParentID = d.ID
LEFT JOIN cow e ON d.ParentID = e.ID
....
WHERE a.ID = ?


Not a very efficient way. Nikolai's suggestion will probably yield better
result, although you have to deal with the data consistency issue.

[Craig Keightley]

Re: Breadcrumbs...h elp!

you can do a select sql statement using the JOIN method
Basically it joins one table to another based on a unique refrence. check
the mysql manual, its in there

hth

Craig

"James" <james@nowhere. com> wrote in message
news:dmvzc.6076 $US1.4314@fed1r ead02...[color=blue]
> If it helps at all, here's an example the array I'm working with. I'd like
> to try to to do it using just the array (i.e., without additional db[/color]
calls).[color=blue]
> An example breadcrumb trail might be:
>
> Home > Food > Fruits > Banana
>
> Here's the array:
>
> Array
> (
> [0] => Array
> (
> [ID] => 7
> [UserID] => 1
> [ParentID] => 0
> [Title] => Food
> )
>
> [1] => Array
> (
> [ID] => 2
> [UserID] => 1
> [ParentID] => 0
> [Title] => Test
> )
>
> [2] => Array
> (
> [ID] => 6
> [UserID] => 1
> [ParentID] => 0
> [Title] => Test
> )
>
> [3] => Array
> (
> [ID] => 5
> [UserID] => 1
> [ParentID] => 0
> [Title] => Test
> )
>
> [4] => Array
> (
> [ID] => 8
> [UserID] => 1
> [ParentID] => 7
> [Title] => Fruits
> )
>
> [5] => Array
> (
> [ID] => 9
> [UserID] => 1
> [ParentID] => 7
> [Title] => Vegetables
> )
>
> [6] => Array
> (
> [ID] => 10
> [UserID] => 1
> [ParentID] => 8
> [Title] => Banana
> )
>
> [7] => Array
> (
> [ID] => 11
> [UserID] => 1
> [ParentID] => 9
> [Title] => Test
> )
>
> )
>
>
>
>
>
>
> "James" <james@nowhere. com> wrote in message
> news:bGuzc.5470 $US1.623@fed1re ad02...[color=green]
> > Got a table of with an ID column and a ParentID column. The ParentID[/color]
> column[color=green]
> > of a record points to the ID of another record. They can form chains
> > several records deep.
> >
> > Is there any particularly good way to retrieve a table containing a list[/color]
> of[color=green]
> > crumbs in the breadcrumb trail, in sequence?
> >
> > If at all possible, I'd like to do it with a single database query. I[/color]
> know[color=green]
> > this probably isn't feasible, but I'd love it if anyone happens to know[/color][/color]
a[color=blue][color=green]
> > way to do it!
> >
> > Thanks,
> > James
> >
> >[/color]
>
>[/color]