Tree structure

[Steve]

I'm looking for some advice on how to design a set of tables to represent a
tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The
type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP
and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

[Steve]

Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisbit_stephenmcnabb.com> wrote in message
news:40**********************@news-text.dial.pipex.com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

[Steve]

Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisbit_stephenmcnabb.com> wrote in message
news:40**********************@news-text.dial.pipex.com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

[Steve]

Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisbit_stephenmcnabb.com> wrote in message
news:40**********************@news-text.dial.pipex.com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve