Using self-joins to make a navigation heirarchy...

I'm not sure if this is the appropriate place to ask this. If it isn't
could you suggest another group?

I've been looking for ways to make a hierarchical navigation system for
a web site. Basically a content management type of thing, with unlimited
numbers of sub-levels.

Lets say I have a table with 3 cols;

id, name and parent

Lets fill it with some data

1 cars NULL
2 bikes NULL
3 trucks NULL
4 Ford 1
5 GM 1
6 Renault 1
7 Focus 4
8 Fiesta 4
9 Turbo 7
10 GT 7

I know the code to create a self-join to show that the Turbo is a Focus.
What I want to be able to do is create additional rows to say its a
Ford, and its a car.

My code (hopefully adapted correctly for the example above)

SELECT a.name AS model, b.name AS type FROM my_table AS a, my_table AS
b WHERE a._id = b.parent

Should give me the car and the model. How can I adapt this to shown
columns for the manufacture and the type of vechicle? And how would you
make it flexible so you could keep adding "sub-levels"

Or is this just completely the wrong way to go abut this?

Bonge Boo wrote:
> I'm not sure if this is the appropriate place to ask this. If it isn't
> could you suggest another group?
>
> I've been looking for ways to make a hierarchical navigation system for
> a web site. Basically a content management type of thing, with unlimited
> numbers of sub-levels.
>
> Lets say I have a table with 3 cols;
>
> id, name and parent
>
> Lets fill it with some data
>
> 1 cars NULL
> 2 bikes NULL
> 3 trucks NULL
> 4 Ford 1
> 5 GM 1
> 6 Renault 1
> 7 Focus 4
> 8 Fiesta 4
> 9 Turbo 7
> 10 GT 7
>
> I know the code to create a self-join to show that the Turbo is a Focus.
> What I want to be able to do is create additional rows to say its a
> Ford, and its a car.
>
> My code (hopefully adapted correctly for the example above)
>
> SELECT a.name AS model, b.name AS type FROM my_table AS a, my_table AS
> b WHERE a._id = b.parent
>
> Should give me the car and the model. How can I adapt this to shown
> columns for the manufacture and the type of vechicle? And how would you
> make it flexible so you could keep adding "sub-levels"
>
> Or is this just completely the wrong way to go abut this?

You're describing what Mark Hillyer calls the Adjacency model in his
article at:
http://dev.mysql.com/tech-resources/...ical-data.html

He also discusses the Nested set model that I plan to use in an upcoming
project.

There is also a webinar on this on the MySQL dev site.

Chad


--
Chad Hanna
Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
FreeBSD Apache MySQL Perl mod_perl PHP

Bonge Boo wrote:
> I'm not sure if this is the appropriate place to ask this. If it isn't
> could you suggest another group?
>
> I've been looking for ways to make a hierarchical navigation system for
> a web site. Basically a content management type of thing, with unlimited
> numbers of sub-levels.
>
> Lets say I have a table with 3 cols;
>
> id, name and parent
>
> Lets fill it with some data
>
> 1 cars NULL
> 2 bikes NULL
> 3 trucks NULL
> 4 Ford 1
> 5 GM 1
> 6 Renault 1
> 7 Focus 4
> 8 Fiesta 4
> 9 Turbo 7
> 10 GT 7
>
> I know the code to create a self-join to show that the Turbo is a Focus.
> What I want to be able to do is create additional rows to say its a
> Ford, and its a car.
>
> My code (hopefully adapted correctly for the example above)
>
> SELECT a.name AS model, b.name AS type FROM my_table AS a, my_table AS
> b WHERE a._id = b.parent
>
> Should give me the car and the model. How can I adapt this to shown
> columns for the manufacture and the type of vechicle? And how would you
> make it flexible so you could keep adding "sub-levels"
>
> Or is this just completely the wrong way to go abut this?

I think this table should just be a list of IDs and parents. 'JOIN' the
separate 'make' and 'model' tables onto the query.

This is an 'adjacency list model' and so requires recursion to obtain
the children of a given parent. This can be done using a php script,
for instance, like this (adapted from Gijs Van Tulder's article at
http://www.sitepoint.com/article/hie...ata-database):

<?php
// $parent is the parent of the children we want to see
// $level is increased when we go deeper into the tree,
// and is used to display a nice indented tree
function display_children($parent, $level) {
// retrieve all children of $parent
$result = mysql_query('SELECT id FROM my_table '.
'WHERE parent="'.$parent.'";');

// display each child
while ($row = mysql_fetch_array($result)) {
echo "<br>";
// indent and display the title of this child
echo str_repeat("&nbsp;&nbsp;",$level).$row['title']."\n";

// call this function again to display this
// child's children
display_children($row['title'], $level+1);
}
}

//Usage example

include('path/to/my/connection/script');

display_children(NULL,0);
?>

In answer to your last question, this isn't completely wrong, but there
are other ways to go about it - see Mike Hillyer's article at
http://dev.mysql.com/tech-resources/...ical-data.html

snip

> In answer to your last question, this isn't completely wrong, but there
> are other ways to go about it - see Mike Hillyer's article at
> http://dev.mysql.com/tech-resources/...ical-data.html

Many thanks to all...

Chad Hanna wrote:
> There is also a webinar on this on the MySQL dev site.
>

Hi chad, have you got a url for this?

Captain Paralytic wrote:
> Chad Hanna wrote:
>> There is also a webinar on this on the MySQL dev site.
>>
>
> Hi chad, have you got a url for this?
>

It would appear that the article on hierarchy replaced the webinar. Sorry.

--
Chad Hanna
Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
FreeBSD Apache MySQL Perl mod_perl PHP

Chad Hanna wrote:
> Captain Paralytic wrote:
> > Chad Hanna wrote:
> >> There is also a webinar on this on the MySQL dev site.
> >>
> >
> > Hi chad, have you got a url for this?
> >
>
> It would appear that the article on hierarchy replaced the webinar. Sorry.
>
> --
> Chad Hanna
> Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
> FreeBSD Apache MySQL Perl mod_perl PHP

It's still out there somewhere though. I saw it just the other day.