codeigniter query within foreach loop which will return a sigle array

Question

i am trying to access all the sub category under category but not working for me. Have written the code like that,

function getAllSubCategory($CatId)
    {
        $data=array();
        $this->db->select('c.catId,c.cat_name,c.cat_alias,c.cat_image,c.parentcatid,c.isHeading');
        $this->db->from('coupon_category as c');
        $this->db->where('c.parentcatid', $CatId);
        $query = $this->db->get();
        if($query->num_rows()>0)
        {
            $data = $dataFetched = $query->result_array();
            $query->free_result();  
            foreach ($dataFetched as $row) {
                $this->db->select('c.catId,c.cat_name,c.cat_alias,c.cat_image,c.parentcatid,c.isHeading');
                $this->db->from('coupon_category as c');
                $this->db->where('c.parentcatid', $dataFetched->catId);
                $data = $query->result_array();
                $query->free_result();  
            }
        }
        echo count($data);
        return $data;

    }

But not working, don't know where i am wrong.

i think you overwrite the array here - ` $data = $query->result_array();`and don't run the query in the loop — splash58, Jan 08 '16 at 19:29
Can you clarify what you want? Do you want only the direct child categories, or do you want the whole tree of sub,sub,... categories? It would be helpful if you provided an example of your data, and expected output. — trincot, Jan 08 '16 at 19:57

score 0 · Answer 1 · answered Jan 08 '16 at 20:15

0

The problem is here

  $this->db->where('c.parentcatid', $dataFetched->catId);

Should be

 $this->db->where('c.parentcatid', $row ["catId"]);

answered Jan 08 '16 at 20:15

Evgeny

3,910
2
20
37

score 0 · Answer 2 · edited May 23 '17 at 12:15

The code has following issues:

The inner loop assigns the new result set to the variable $data and thereby you lose the previous values you had in $data;
You repeat exactly the same query again in each iteration of that loop. You do not use the $row variable in the loop;
There is no support for deeper nested subcategories, which the data structure seems to allow for.

If you want to fetch child categories, and their child categories, ... etc, you should use recursion, and even if you would only have two levels of subcategories in your table, it will still be good code.

Here is some code for making it work in the direction you were going, using recursion, and using the function array_merge to append the data as you go.

function getAllSubCategory($CatId)
{
    $this->db->select('c.catId,c.cat_name,c.cat_alias,c.cat_image,c.parentcatid,c.isHeading');
    $this->db->from('coupon_category as c');
    $this->db->where('c.parentcatid', $CatId);
    $query = $this->db->get();
    $dataFetched = $query->result_array();
    $query->free_result();
    $data = array();
    foreach ($dataFetched as $row) {
        $data[] = $row;
        $data = array_merge($data, getAllSubCategory($row["catId"]));
    }
    echo count($data);
    return $data;
}

Alternative

You could get better performance by doing a hierarchical query on the database (as for instance was asked for here ), but I don't know if it can be made to work with Active Record. So I will provide the code using plain query() syntax, according to the answer I have given myself to that particular question:

function getAllSubCategory($CatId)
{
    $CatId = $this->db->escape($CatId); // sanitise to prevent SQL injection
    $query = $this->db->query(
        "select c.catId,c.cat_name,c.cat_alias,c.cat_image,c.parentcatid,c.isHeading
         from   (select * from coupon_category
                 order by parentcatid, catId) as c,
                (select @pv := '$CatId') as initialisation
         where  find_in_set(c.parentcatid, @pv) > 0
         and    @pv := concat(@pv, ',', catId)");
    $data = $query->result_array();
    $query->free_result();
    echo count($data);
    return $data;
}

Note that the last solution requires that for each record parentcatid < catid, otherwise the results will not be complete.

But if that is the case, this will certainly beat the first solution in performance, as it only executes one database query.

Also, if your table would have cycles where a child of a node is also its parent (for example), the first solution will keep looping and querying until some out-of-memory error occurs. The last solution will not, because it only looks for children for which parentcatid < catid.

codeigniter query within foreach loop which will return a sigle array

2 Answers2

Alternative