Advanced(?) MySQL Query.... Query

[Jimmy_uk]

Hello,

 

Hopefuly someone here will be able to help me.

I'm trying to produce a PHP string from an SQL query. I have a category table and want to make an array or string that has all the child categories in it so that my menu can open the ID numbers upto the desired category

 

My table is like this (Sample Data)

 

Name: stockcats

 

ID |ParentID | Name

-----------------------------

1 |0 | Office Items

2 |0 | Electrical Supplies

3 |1 | Paper

4 |3 | Copier Paper

5 |3 | Photo Paper

6 |5 | Glossy Finish

 

 

So if i ask for the childs to "Glossy Finish" then i would like the query to return the rows with IDs: 6,5,3,1

 

I hope i've made this clear enough. I don't want the client to be restricted by the amount of category levels.

 

Many thanks

[RichardRotterdam]

The following article helped me a lot with that exact issue you're having

http://www.sitepoint.com/article/hierarchical-data-database/

 

The easiest way would be by using a recursive function although it would be inefficient since it would be using a bunch queries.

You probably should go with the Tree Traversal option.

 

Personally I use doctrine framework and the NestedSet feature to handle hierarchical data for me but that's only if you're interested in using ORM.