By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

Breadcrumbs...help!

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <ja***@nowhere.com> wrote in message
news:bGuzc.5470$US1.623@fed1read02...
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

Jul 17 '05 #2

P: n/a
"James" <ja***@nowhere.com> wrote in message
news:<bGuzc.5470$US1.623@fed1read02>...

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?


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
Jul 17 '05 #3

P: n/a

"James" <ja***@nowhere.com> wrote in message
news:bGuzc.5470$US1.623@fed1read02...
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


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.
Jul 17 '05 #4

P: n/a
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" <ja***@nowhere.com> wrote in message
news:dmvzc.6076$US1.4314@fed1read02...
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" <ja***@nowhere.com> wrote in message
news:bGuzc.5470$US1.623@fed1read02...
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


Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.