Listing a mysql result in a select box

[jimbeeer]

I have a db table with 3 fields:

 

business_folder_id            [auto-inc]

business_folder_name      [text]

business_folder_parent    [int]

 

This allows me to create and put folders within folders. Basically build a directory tree with a db.

 

What I want to do now is list every db item in a <select> box with subfolders tabbed along slightly.

 

I've also displayed the id and parent of each entry to better explain what I'm trying to do.

 

E.g.

 

Cafes [id:1, parent:0]

-> Franchises [id:2, parent:1]

---> Large Franchises [id:3, parent:2]

---> Small Franchises [id:4, parent:2]

-> Takeaways [id:5, parent:1]

Restaurants [id:6, parent:0]

Small Shops [id:7, parent:0]

-> Hardware Shops [id:8, parent:7]

-> Newsagents [id:9, parent:7]

Sweet Shops [id:10, parent:0]

 

So basically it needs to check along each item, then see if that parent has any children, if so then list them, then carry back on with the list. But the part that's getting me the most is it's not limited to 1 level of parent-child. It's infinite.

 

If a business doesn't have a parent then its parent is set as 0, otherwise it's set as the id of its parent.

 

Can someone help me with this please. I've been at it for the last 4 hours and I'm still no closer.

 

Big huge thanks in advance...

 

-James

[scootstah]

With that method, you will have to run a query for every iteration of the result loop, which is never a good idea. Take a look at these two articles:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

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

[jimbeeer]

I found the following function worked absolutely perfectly:

 

function createMenu($nodes, $all, $spacing=''){ 
    //spacing is there to just space the source html. it could 
    //be taken off if needed. 
    //open the ul tag 
    echo "$spacing\n"; 
    //loop through nodes 
    foreach($nodes as $n){ 
        //echo the node name 
        echo "<option value=\"{$n['bus_folder_id']}\">$spacing {$n['bus_folder_name']}</option>\n"; 
        //check if children exist under this node 
        if(isset($all[$n['bus_folder_id']])){ 
            //add to the spacing (used for html beautification only) 
            $spacing .= '  '; 
            //if so, call createMenu again and create a menu of 
            //those child nodes 
            createMenu($all[$n['bus_folder_id']], $all, $spacing); 
            //shorten spacing (used for html beautification only) 
            $spacing = substr($spacing, 7); 
       } 
    } 
    //close the ul 
    echo "$spacing\n"; 
} 

 

Then I call the function with:

<?php createMenu($nodes[0], $nodes); ?>

 

This displays them nicely in a select dropdown with a double space before each subfolder.