[SOLVED] Displaying product categories

[fooDigi]

is there a more efficient way?

i have a hierarchy of product categories... in the db table, each has an ID, Name, and ParentID, like so...

 

ID|Name|ParentID

_________________

1|Home & Garden|0

2|Books|0

3|Kitchen & Dining|1

4|Bedding & Bath|1

5|Textbooks|2

6|Magazines|2

etc ... and they go about 5 levels deep right now, but theoretically could go much much deeper

 

a '0' ParentID means it is a top level category, otherwise it corresponds to another category which is it's parent... is there a better way to structure the data and/or code this? the code is below, and it is just to display a category tree, but it results in many redundant queries... i don't want to run 50+ queries every time i want to display the tree...

 

$top_res = mysql_query("select CategoryID, CategoryName from tblCategory_test where ParentID = 0");

while($r = mysql_fetch_array($top_res))
{
    echo $r['CategoryName'].'<br />';
    $sub_res = mysql_query("select * from tblCategory_test where ParentID = $r[CategoryID]");
    while($sr = mysql_fetch_array($sub_res))
    {
        echo '|'.$sr['CategoryName'].'<br />';
        $ssub_res = mysql_query("select * from tblCategory_test where ParentID = $sr[CategoryID]");
        while($ssr = mysql_fetch_array($ssub_res))
        {
            echo '||'.$ssr['CategoryName'].'<br />';
            $sssub_res = mysql_query("select * from tblCategory_test where ParentID = $ssr[CategoryID]");
            while($sssr = mysql_fetch_array($sssub_res))
            {
                echo '|||'.$sssr['CategoryName'].'<br />';
                $ssssub_res = mysql_query("select * from tblCategory_test where ParentID = $sssr[CategoryID]");
                while($ssssr = mysql_fetch_array($ssssub_res))
                {
                    echo '||||'.$ssssr['CategoryName'].'<br />';
                }
            }
        }
    }
}

this works, but what if there is a category 10 levels deep? do i have to have nested loops to support that level?

thanks for any help...

[Zane]

http://www.phpfreaks.com/forums/index.php/topic,271192.msg1280199.html#msg1280199

[fooDigi]

thanks zanus, that seemed like a very good solution to begin with... but it only works if you have only one level of sub-categories... i will try and tweak... thanks.

[Zane]

yeah, didn't think about the other layers part. sorry

[fooDigi]

found exactly what i was looking for... a great in depth article on managing hierarchical data in mysql...

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html