Recursive Function To Create Array

Question

Hi, i use kohana framework and i am trying to code recursive function to create category tree.

My Categories Table

id      int(11)     NO  PRI     NULL    auto_increment
name    varchar(50)     NO      NULL     
parent_id   int(11)     NO      NULL     
projects_count  int(11)     NO      NULL     

My Example Which Is Not Work

public static function category_list($parent_id = 0)
{
    $result =  Database::instance()->query('
        SELECT name, projects_count 
        FROM project_categories
        WHERE parent_id = ?', 
        array($parent_id)
    );

    $project_categories = array();
    foreach($result as $row)
    {
        $project_categories[] = $row;

        Project_Categories_Model::factory()->category_list($parent_id + 1);
    }

    return $project_categories;
}

Answer 1

Hi, using this kind of hierarchical data implementation is high not optimal.

because to get every subcategory you need do a query to database. like here you want to create recursion function.

if you still can change your table architecture please check this article: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

there is described a solution, how to fetch all hierarchy in one time query. so the recursive function will be unnecessary