Get all categories (multilevel)

The simplest solution to your problem would be to add recursion.

public function getCategoryTreeForParentId($parent_id = 0) {  $categories = array();  $this->db->from('categories');  $this->db->where('parent_id', $parent_id);  $result = $this->db->get()->result();  foreach ($result as $mainCategory) {    $category = array();    $category['id'] = $mainCategory->id;    $category['name'] = $mainCategory->name;    $category['parent_id'] = $mainCategory->parent_id;    $category['sub_categories'] = $this->getCategoryTreeForParentId($category['id']);    $categories[$mainCategory->id] = $category;  }  return $categories;}

This approach can be greatly increased in speed by preloading all categories and operating over the array, thus skipping the query for each new parent_id.

Also i did not use the objects of codeigniter, as i don't know them. If they support magic setters / getters or can be convinced to take a array of child objects, they should be used instead the the array i build here.

What the algorithm does is: load all categories with a given parent_id, loop through all those categories, assume the iteration-category-id as parent_id and load everything for it.This effectively loads all categories, as long as they reference existing categories.

There is a slight danger involved: When you have construct of categories A and B where A has B as parent and B has A as parent, you will run into an endless loop, as they load each other again and again. This forces you to have a clean tree structure in your data.

Update

As this still gets upvotes:There is another advice concerning this implementation.If your category tree is bigger or has multiple levels, you might run into performance issues, as this implementation loads the categories again and again with new query parameters. This can quite easily result in dozens, hundreds or thousands of queries, depending on your category tree.

A quite more efficient way is to load all categories (with one query) from your category table and sort them with recursion within your application. This is one of the rare cases, where early evaluation does improve performance.

If the tree is required more than once within the same request, one could even add caching through static variables (with all the usual dangers of caching).

This is a codeigniter library i found it in an answer here in stackoverflow but i don't remember its owner. I made some modification to it to return the nested categories as a list. its works very well with me.

<?phpif (!defined('BASEPATH'))    exit('No direct script access allowed');class Tree {    var $table = '';    var $CI = '';    var $ul_class = '';    var $iteration_number = 0;    function Tree($config = array()) {        $this -> table = $config['table'];        $this -> CI = &get_instance();        $this -> CI -> load -> database();        $this -> ul_class = $config['ul_class'];    }    function getTree($parent_id = 1) {        $this -> iteration_number++;        $this -> CI -> db -> where('parent_id', $parent_id);        $first_level = $this -> CI -> db -> get($this -> table) -> result();        if($this->iteration_number == 1)            $tree = '<ul id="red" class="' . $this -> ul_class . '">';        else            $tree = '<ul>';        foreach ($first_level as $fl) {            $this -> CI -> db -> where('parent_id', $fl -> category_id);            $count = $this -> CI -> db -> count_all_results($this -> table);            if ($count != 0) {                $tree .= '<li><span>' . $fl -> category_name . '</span>';                $tree .= $this -> getTree($fl -> category_id);            } else {                $tree .= '<li><a href="'.base_url().'categories/sub/'.str_replace(' ', '-', $fl -> category_name).'" cat_id="'.$fl -> category_id.'" class="leaf">' . $fl -> category_name . '</a>';            }            $tree .= '</li>';        }        $tree .= '</ul>';        return $tree;    }} ?>

You can use it from your controller as the following:

$config['ul_class']='treeview';$config['table']='categories';$this->load->library('tree',$config);$tree = $this->tree->getTree();

and the structure of the DB table as the following:

CREATE TABLE IF NOT EXISTS `categories` (  `category_id` int(4) NOT NULL AUTO_INCREMENT,  `category_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `category_description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,  `parent_id` int(4) NOT NULL,  PRIMARY KEY (`category_id`),  KEY `categories_categories_fk_idx` (`parent_id`)) ENGINE=InnoDB 

You can modify the returned result by the getTree function in the library to return what you need.

I'm going to offer up a different solution, although you'll need to do some slight restructuring:

Using a lineage-style hierarchy, you can avoid all of the recursion issues & have much faster data pulls. You mark each category with a full list of ancestors, such as: 0001-0005-0015 ... etc and then can run simple queries with a LIKE to get your subqueries.

I have a Codeigniter library on git & blog post explaining how it all works: http://codebyjeff.com/blog/2012/10/nested-data-with-mahana-hierarchy-library