Parsing Hierarchical Data

[GlassCasket]

I read http://www.sitepoint.com/article/hierarchical-data-database and had a few questions. I posted on their forums no one seems to be able to help.

 

Great article, read it several times.

 

I have the following structure:

 

HTML Code:

- TB
    - cat a
    - cat b
        - sub-cat b
        - sub-cat b
        - sub-cat b
    - cat c

 

My table structure is 'category_id, name, lft, rgt'.

 

The category_id is linked in with my products table.

 

Any ideas how I can display my products in the following fashion:

 

HTML Code:

 

- TB
    - cat a
        - product from sub category a
    - cat b
        - sub-cat bb
            - product from sub category b
        - sub-cat bbb
            - product from sub category bbb
            - product from sub category bbb
        - sub-cat bbbb
    - cat c
        - product from sub category c

 

Thanks!

[GlassCasket]

Anyone please? I've asked this many places but no one seems to know.

 

And if I'm not being clear enough, just ask.

 

Thanks!

[Fadion]

Not that im very good with this stuff, but maybe i can think of smth. U have two tables, one for the products and one for the categories? Each product in your product table has a field which references the categories table?

[Fadion]

Initially i thought using a query like this:

 

SELECT cat.category_id, cat.category_name, prod.cat_id, prod.name FROM cat, prod WHERE prod.cat_id=cat.category_id ORDER BY category_id ASC

 

but your aim is to have sub categories and stuff which makes it difficult for me. Probably someone else may resolve it.