return Unlimited subcategory array

[eluser]Unknown[/eluser]
I'm working on a shopping cart project. I have a category table and I would like to have a dropdown with like this:

Code:

-Women
--Clothing
---Dresses
---Tops
---Knitwear
---Skirts
---Lingerie
--Accessories
---Jwellery
---Beauty & Cosmetics
---Tights and socks
-Men
--Clothing
---T-shirts & Polo shirts
---Hoodies & Sweats
---Shirts
---Jeans
---Trousers & Shorts
--Accessories
---Belts
---Ties
---Sunglasses
-Teen

Category table structure:

Code:

CREATE TABLE IF NOT EXISTS `categories` (
  `catid` int(11) NOT NULL auto_increment,
  `catname` varchar(128) NOT NULL,
  `parentid` int(11) NOT NULL,
  PRIMARY KEY  (`catid`)
)

data is like this:

Code:

catid    catname                        parentid
1        Women                          0
2        Men                            0
3        Clothing                       1
4        Accessories                    1
5        Clothing                       2
6        Accessories                    2
7        Dresses                        3
8        Tops                           3
9        Knitwear                       3
10       Skirts                         3
11       Lingerie                       3
12       Jwellery                       4
13       Beauty & Cosmetics             4
14       Tights and socks               4
15       T-shirts & Polo shirts         5
16       Hoodies & Sweats               5
17       Shirts                         5
18       Jeans                          5
19       Trousers & Shorts              5
20       Belts                          6
21       Ties                           6
22       Sunglasses                     6
43       Teen                           0

My function is as below:

Code:

function getcategorylist($parentid, $space="") {

        $this->db->where('parentid', $parentid);
        $query = $this->db->get('categories');

        $numrows = $query->num_rows();

        if ($numrows > 0) {

            $space .= "-";
            foreach ($query->result_array() as $row) {

                $data['catid'] = $row['catid'];
                $data['catname'] = $row['catname'];
                $data['dispcatname'] = $space . $row['catname'];
                $data['parentid'] = $row['parentid'];
                
                $this->getcategorylist($row['catid'], $space);
                
            }
        }

        return $data;
    }

When I try to echo the array value,I'm only getting the last row.
-------

But when I tried to

Code:

echo $data['catname']

after the line

Code:

$data['parentid'] = $row['parentid'];

I was able to my list. But I dont want to echo the result from a model function. I would like to store the sorted hierarchy in an array and return it to the controller and then pass it to the view.

Please help!!!

[eluser]Bart Mebane[/eluser]
Your $data array only saves the last row because it is local to the function and isn't preserved during the recursive calls. The array and its index need to be declared static. (This is edited. I originally made them class properties, but static is better in this case.) The array also needs to be 2-dimensional in order to hold all the categories and the information about each category.

Code:

function getcategorylist($parentid, $space = "") {

        static $data = array();
        static $index = 0;

        $this->db->where('parentid', $parentid);
        $query = $this->db->get('categories');

        $numrows = $query->num_rows();

        if ($numrows > 0) {
            
            $space .= "-";
            foreach ($query->result_array() as $row) {

                $data[$index]['catid'] = $row['catid'];
                $data[$index]['catname'] = $row['catname'];
                $data[$index]['dispcatname'] = $space . $row['catname'];
                $data[$index]['parentid'] = $row['parentid'];
                $index++;
                $this->getcategorylist($row['catid'], $space);
                
            }
        }

        return $data;
    }

If you're going to have a lot of categories, it would be much more efficient to just hit the database once and get all the rows, and then loop through them recursively to build the $data array.

[eluser]Unknown[/eluser]
Thanks Bart Mebane.

it would be much more efficient to just hit the database once and get all the rows, and then loop through them recursively to build the $data array.

This logic has came in to my mind; but I couldn't make it. Could you please give an example with my current table?

Thanks in advance.

[eluser]Bart Mebane[/eluser]
It's not too much different from what you already had, but it takes the query out of the loop. I don't have any way to verify this, so be sure to check the results.

Code:

function getcategorylist()
{
    $this->db->order_by('parentid');
    $query = $this->db->get('categories');
    return $this->_getcategories($query->result_array());
}
            
private function _getcategories($rows, $parentid = 0, $space = "")
{
    static $data = array();
    static $index = 0;

    $space .= "-";
    foreach ($rows as $row)
    {
        if ($row['parentid'] > $parentid)
        {
            break;
        }
        if ($row['parentid'] == $parentid)
        {
            $data[$index]['catid'] = $row['catid'];
            $data[$index]['catname'] = $row['catname'];
            $data[$index]['dispcatname'] = $space . $row['catname'];
            $data[$index]['parentid'] = $row['parentid'];
            $index++;
            $this->_getcategories($rows, $row['catid'], $space);
        }
    }
    return $data;
}

[eluser]cahva[/eluser]
Heres 2 methods in model that you can use to create an array which you can use to create category tree in view:

Code:

function get_categorytree($parent_id = 0)
{
    $return_arr = array();

    $query = $this->db->where('parentid',$parent_id)->get('categories');


    foreach ($query->result() as $r)
    {
        $arr = array(
            'catid' => $r->catid,
            'catname' => $r->catname
        );

        if ($this->has_children($r->catid))
        {
            $children = $this->get_categorytree($r->catid);
            if ($children)
            {
                $arr['children'] = $children;
            }
        }

        $return_arr[] = $arr;

    }

    return $return_arr;

}

function has_children($id)
{
    $this->db->where('parentid',$id);
    $this->db->from('categories');

    return $this->db->count_all_results();

}

Its modified from my own code to match your categories table and did not test it. It should give you nice array of your categories something like this:

Code:

Array
(
    [0] => Array
        (
            [catid] => 1
            [catname] => Women
            [children] => Array
                (
                    [0] => Array
                        (
                            [catid] => 21
                            [catname] => Clothing
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [catid] => 7
                                            [catname] => Dresses
                                        )
                                        
                                    [1] => Array
                                        (
                                            [catid] => 3
                                            [catname] => Tops
                                        )
                                )
                        )
                        
                    [1] => Array
                        (
                            [catid] => 4
                            [catname] = Accessories
                            [children] => Array
                                (
                                    ...
                                )
                        )
                )
        )
        
    [1] => Array
        (
            [catid] => 2
            [catname] => Men
            [children] => Array
                (
                    ...
                )
        )
)

Now you have nice multidimensional array that you can use in various ways. For example you can create a helper something like in this post.