Recursive category tree Code

[eluser]tmsajin[/eluser]
Usage :: $this->category_library->showCategories(45);
output:: a select menu with all subcategories inside the specified category

Code
~~~~~~~~~~~~~~~~~~~~~~~~~


i used adodb as db library , and following is the table structure. copy the following functions in either library or model and use as above.



#----------------------------
# Table structure for categories
#----------------------------
CREATE TABLE `categories` (
`cat_id` bigint(20) unsigned NOT NULL auto_increment,`cat_name` varchar(255) default NULL,
`cat_nick` bigint(20) unsigned default NULL,`cat_description` text,`cat_parentid` bigint(20) unsigned default NULL,`cat_count` int(10) unsigned default '0',`cat_path` text,`cat_id_path` blob,PRIMARY KEY (`cat_id`)
)

ENGINE=InnoDB DEFAULT CHARSET=latin1;
#----------------------------
# Records for table categories
#----------------------------


insert into categories values (45, 'Category 1', null, null, 0, 0, null, null),
(46, 'Category2', null, null, 45, 0, null, null),
(47, 'Category 3', null, null, 46, 0, null, null);

Code:

// &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
    // this is recursive category selection part
    function untree($parent, $level)
    {    
                $obj = & get_instance();
                $sql = "select cat_id, cat_name as name from categories where cat_parentid=$parent order by cat_name";
                $parentsql = $obj->adodb->Execute($sql);
                // if it is a leaf (no data underneath it) then return
                if ($parentsql === false)
                    return false;  
                //else echo the result, and recurse the function (so to speak)
                else
                    {
                        while(!$parentsql->EOF)
                        {
                        $branch = $parentsql->fields;    
                        //give it some indents in the select box
                        $this->comv.="\r\n<option value=\"".$branch[0]."\">";
                        for ($x=1; $x<=$level; $x++)
                            {
                                $this->comv.="+";
                            }
                        $this->comv.=$branch[1]."</option>";
                        //now run this function and find everthing where the parent is equal to the ID of this entry
                        $rename_level = $level;
                        $this->untree($branch[0], ++$rename_level);
                        $parentsql->MoveNext();
                        }
                    }
        }
    

    // show category tree from db
    // return as select item
    function showCategories($selected_category) {
    $this->comv.="<select>";
    // un tree so to speak this data tree
        
    /* AND HERE IS HOW YOU CALL THE FUNCTION */
    $obj = & get_instance();
    $sql = "select *,cat_name as name from categories where cat_parentid=$selected_category order by cat_name";
    // get ALL the entries that have NO parent
    $compsql = $obj->adodb->Execute($sql);
        while (!$compsql->EOF)
        {
            $this->comv.="<option>".$compsql->fields['name']."</option>";
            //call the untree function and find all the entries that have THIS entry as a parent (therefore those entries are children of this one)
            $this->untree($compsql->fields['cat_id'],1);
             $compsql->MoveNext();
        }
        $this->comv.="</select>";
        echo $this->comv;
    } // end of showCategories()
    
    // &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Hope somebody who is in hurry , can use this.