PHP recursive function to retrieve all children of a category

Question

I would like to write a recursive PHP function to retrive all the children for the specified category. I tried the one described here but it didn't output what I have expected.

My categories table looks like this:

CREATE TABLE `categories` (
 `category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `category_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_slug` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_parent` smallint(5) unsigned NOT NULL DEFAULT '0',
 `category_description_ro` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_description_en` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

Bellow is an example of data in the table:

category id | category name | category_parent

1            Categoria 1            0        
2            Categoria 2            0        
3            Categoria 3            0        
4            Categoria 1.1          1        
5            Categoria 1.2          1        
6            Categoria 1.3          1        
7            Categoria 1.1.2        4 

Thanks.

Answer 1

function get_categories($parent = 0)
{
    $html = '<ul>';
    $query = mysql_query("SELECT * FROM `categories` WHERE `category_parent` = '$parent'");
    while($row = mysql_fetch_assoc($query))
    {
        $current_id = $row['category_id'];
        $html .= '<li>' . $row['category_name'];
        $has_sub = NULL;
        $has_sub = mysql_num_rows(mysql_query("SELECT COUNT(`category_parent`) FROM `categories` WHERE `category_parent` = '$current_id'"));
        if($has_sub)
        {
            $html .= get_categories($current_id);
        }
        $html .= '</li>';
    }
    $html .= '</ul>';
    return $html;
}

print get_categories();

Answer 2

I've found quite a simple example that works over an array of categories retrieved from a DB. The query "SELECT * FROM categories" will create an array of all the categories.

In my categories table I have the fields 'id','parent_id'and 'name'. That's all. Base categories have parent id set as 0. Subcats have parent_id set as parent category ID. Simple right ? Here is the code :

$categories = [

            ['id'=>1,'parentID'=>0, 'name' => 'Main 1' ],
            ['id'=>2,'parentID'=>0, 'name' => 'Main 2' ],
            ['id'=>3,'parentID'=>0, 'name' => 'Main 3' ],
            ['id'=>4,'parentID'=>0, 'name' => 'Main 4' ],
            ['id'=>5,'parentID'=>1, 'name' => 'sub 1 - 1' ],
            ['id'=>6,'parentID'=>1, 'name' => 'sub 1 - 2' ],

            ['id'=>11,'parentID'=>5, 'name' => 'sub 1 - 2' ],
            ['id'=>12,'parentID'=>5, 'name' => 'sub 1 - 2' ],
            ['id'=>13,'parentID'=>5, 'name' => 'sub 1 - 2' ],
            ['id'=>14,'parentID'=>6, 'name' => 'sub 1 - 2' ],


            ['id'=>7,'parentID'=>2, 'name' => 'sub 2 - 1' ],
            ['id'=>8,'parentID'=>2, 'name' => 'sub 2 - 2' ],
            ['id'=>9,'parentID'=>3, 'name' => 'sub 3 - 1' ],
            ['id'=>10,'parentID'=>3, 'name' => 'sub 3 - 2' ]
        ];

        function getCategories($categories, $parent = 0)
        {
            $html = "<ul>";
            foreach($categories as $cat)
            {
                if($cat['parentID'] == $parent)
                {
                    $current_id = $cat['id'];
                    $html .= "<li>" . $cat['name'] ;
                        $html .= getCategories($categories, $current_id);

                    $html .= "</li>";
                }
            }
            $html .= "</ul>";
            return $html;
        }



        echo getCategories($categories) ;

$categories array is an effect of one sql query "SELECT * FROM categories"

Answer 3

Here is something I use for displaying nested links, it only makes one DB query then caches the results in a useful array..

function generate_menu($parent, $menu_array=Array(), $level = 0, $first=0)
    {
        $has_childs = false;

        if (empty($menu_array)) {

            $rs = mysql_query("SELECT id, parent, name FROM cats");

            while ( $row = mysql_fetch_assoc($rs) )
            {
                    $menu_array[$row['id']] = array('name' => $row['name'],'parent' => $row['parent']);
            }        
        }

        foreach ($menu_array as $key => $value)
        {
            if ($value['parent'] == $parent) 
            {   
                //if this is the first child print '<ul>'           
                if ($has_childs === false) {
                        //don't print '<ul>' multiple times             
                        $has_childs = true;
                        if ($first == 0){
                            echo "<ul id=\"nav\">\n";
                            $first = 1;
                        } else {
                            echo "\n<ul>\n";
                        }
                }
                $pad = str_repeat('&#8211; ', $level);
                echo "<li><a href=\"http://" . $value['url'].'/\">' . $value['name'] . "</a>";
                generate_menu($key, $menu_array, $level + 1, $first);
                //call function again to generate nested list for subcategories belonging to this category
                echo "</li>\n";
            }
        }
        if ($has_childs === true) echo "</ul>\n";
    }