Recursively create a nested Category Tree from a cat_to_cat table?

[DssTrainer]

So I'm in over my head here...

 

Working on an ecommerce application to have Multiple Categories for products. Categories are in a category db table:

MenuID |   Title      |    Icon     |     href
     1    |Computers |   a.png    |   blah.php
     2    |    Intel     |   b.png    |   bleh.php
     3    |     AMD    |   c.png    |   blih.php
     4    |Appliances |   d.png    |   bluh.php
     5    |  Stoves    |   e.png    |   bloh.php
     6    |Microwaves|   f.png    |   blyh.php
     7    |Convection |   g.png    |   blhh.php

 

For nesting, I'd like to create a menu_to_menu table like this:

MenuID  |  ParentID

     1     |      0

     2     |      1

     3     |      1

     4     |      0

     5     |      4

     6     |      4

     7     |      6

 

As you can see, there are more than just 2 levels of subcategorizing.

 

How can I recursively loop through the menu_to_menu table to get a result like this:

 

- Computers

--> Intel

--> Amd

- Appliances

--> Stoves

--> Microwaves

----> Convection

 

 

 

[Barand]

You could just put the parent_id in the menu table.

 

<?php
function listMenu ($parent, level=0)
{
    $sql = "SELECT m.title, m.menu_id
            FROM menu m
            JOIN menu_to_menu mm USING (menu_id)
            WHERE mm.parent_id = $parent";
    $res = mysql_query($sql);
    while (list ($title, $id) = mysql_fetch_row($res))
    {
        $indent = str_repeat('---', $level);
        echo "$indent $title<br/>";
        listMenu ($id, $level+1);
    }
}

// call

listMenu(0);

?>   

[DssTrainer]

Ah.. well that does actually work. But I'm wondering if there is a way to do it with less db queries. I'm thinking if there was some way to pull ALL the menu table and all menu_to_menu stuff and then loop through that to build the menu.

 

[Barand]

do you mean

<?php
mysql_connect('localhost');
mysql_select_db('test3');
$res = mysql_query("SELECT m.menuID, m.title, mm.parentID
            FROM menu m
            JOIN menu_to_menu mm USING (menuID)") or die (mysql_error());
$menus = array();
while (list($mid, $title, $pid) = mysql_fetch_row($res))
{
    $menus[$pid][] = array($mid, $title);
}

function listMenus($parent, &$menus, $level=0)
{
    if (is_array($menus[$parent])) 
    foreach ($menus[$parent] as $items)
    {
        $indent = str_repeat('---', $level);
        echo "$indent{$items[1]}<br/>";
        listMenus($items[0], $menus, $level+1);
    }
}

listMenus(0, $menus);
?>