How to get all the child and grandchild categories of a parent category in codeigniter?

Question

I want to get all the child and grandchild categories of a parent category upto any level. My table structure goes like this.

Can anyone suggest me how to get all the child categories (i.e. Samsung and S3 of phone category).

Answer 1

You need recursivity. Create a function like this (assuming you use active records):

function getCategoriesByParentId($category_id) {
    $category_data = array();

    $category_query = $this->db->query("SELECT * FROM categories WHERE parent_category = '" . (int)$category_id . "'");

    foreach ($category_query->result() as $category) {
        $category_data[] = array(
            'category_id' => $category->category_id,
            'category_name' => $category->category_name,
            'category_alias' => $category->category_alias,
            'parent_category' => $category->parent_category
        );

        $children = getCategoriesByParentId($category->category_id);

        if ($children) {
            $category_data = array_merge($children, $category_data);
        }           
    }

    return $category_data;
}

and call it like this:

$all_categories = getCategoriesByParentId(0);

The output will be like this:

Array
(
    [0] => Array
        (
            [category_id] => 14
            [category_name] => s3
            [category_alias] => s3
            [parent_category] => 13
        )

    [1] => Array
        (
            [category_id] => 13
            [category_name] => Samsung
            [category_alias] => samsung
            [parent_category] => 12
        )

    [2] => Array
        (
            [category_id] => 12
            [category_name] => Phone
            [category_alias] => phone
            [parent_category] => 0
        )

)

For more info, this was taken from OpenCart 1.5.4, class ModelCatalogCategory under catalog\model\catalog\category.php.

Answer 2

Assuming you have these in an array you need to use a recursive loop.

// Information from database loaded into array
$array = array(
    0    => array(
        'category_id'        => 12,
        'category_name'      => 'Phone',
        'category_alias'     => 'phone',
        'parent_category'    => 0
    ),
    // ... other rows
); 
$children = array(); // Will contain all our children and grandchildren

function FindChildrenAndGrandchildren(array $array, $parentId, $level = 0) {
    $children = array();
    foreach($array as $category) {
        if($category['parent_category'] == $parentId) {
            if($level == 0) {
                $temp = FindChildrenAndGrandchildren($array, $category['category_id'], 1);
                $category['children'] = $temp;
            }
            $children[] = $category;
        }
    }
    return $children;
}

$children = FindChildrenAndGrandchildren($array, 12); // Start search