recursive function to get all the child categories

Question

Here is what I'm trying to do: - i need a function that when passed as an argument an ID (for a category of things) will provide all the subcategories and the sub-sub categories and sub-sub-sub..etc. - i was thinking to use a recursive function since i don't know the number of subcategories their sub-subcategories and so on so here is what i've tried to do so far

function categoryChild($id) {

    $s = "SELECT * FROM PLD_CATEGORY WHERE PARENT_ID = $id";
    $r = mysql_query($s);

    if(mysql_num_rows($r) > 0) {

        while($row = mysql_fetch_array($r))
            echo $row['ID'].",".categoryChild($row['ID']);
    }
    else {
        $row = mysql_fetch_array($r);
        return $row['ID'];
    }
}

If i use return instead of echo, i won't get the same result. I need some help in order to fix this or rewrite it from scratch

score 16 · Accepted Answer · answered Mar 07 '10 at 23:28

I had a hard time trying to figure out your function. I think this will do what you want. It gets all the children of a category with ID $id, and also their children (thus getting the whole sub category, sub sub category effect that you wanted).

function categoryChild($id) {
    $s = "SELECT ID FROM PLD_CATEGORY WHERE PARENT_ID = $id";
    $r = mysql_query($s);

    $children = array();

    if(mysql_num_rows($r) > 0) {
        # It has children, let's get them.
        while($row = mysql_fetch_array($r)) {
            # Add the child to the list of children, and get its subchildren
            $children[$row['ID']] = categoryChild($row['ID']);
        }
    }

    return $children;
}

This function will return:

$var = array(
        'categoryChild ID' => array(
                'subcategoryChild ID' => array(
                        'subcategoryChild child 1' => array(),
                        'subcategoryChild child 2' => array()
                )
        ),
        'anotherCategoryChild ID' => array() # This child has no children of its own
);

It basically returns an array with the ID of the child and an array containing the IDs of its children. I hope this is of any help.

score 6 · Answer 2 · edited May 23 '17 at 12:30

6

database tree to multidimensional array

<?php
function getTree($rootid)
{
   $arr = array();

   $result = mysql_query("select * from PLD_CATEGORY where PARENT_ID='$rootid'");
   while ($row = mysql_fetch_array($result)) { 
     $arr[] = array(
       "Title" => $row["Title"],
       "Children" => getTree($row["id"])
     );
   }
   return $arr;
}
?>

edited May 23 '17 at 12:30

Community

1
1

answered Mar 07 '10 at 23:47

Matthew

47,584
11
86
98

score 3 · Answer 3 · answered Jul 19 '11 at 07:26

As this has been brought up by @Pawan Sharma, I thought that I might give some answer as well.

All given solutions suffer from common problem - they perform SQL query for each and every child. E.g., if there are 100 childs in 2nd level, then 100 queries will be done, while it can actually be done in single query by using where parent_id in (<list_of_ids>).

Sample DB:

create table category (
    id          int auto_increment primary key,
    parent_id   int default null,
    title       tinytext,
    foreign key (parent_id) references category (id)
) engine = InnoDB;

insert into category (id, parent_id, title) values
    (1, null, '1'),
    (2, null, '2'),
    (3, null, '3'),
    (4, 1   , '1.1'),
    (5, 1   , '1.2'),
    (6, 1   , '1.3'),
    (7, 4   , '1.1.1'),
    (8, 4   , '1.1.2'),
    (9, 7   , '1.1.1.1');

Here's my solution:

/**
 * @param null|int|array $parentID
 */
function getTree($parentID) {
    $sql = "select id, parent_id, title from category where ";
    if ( is_null($parentID) ) {
        $sql .= "parent_id is null";
    }
    elseif ( is_array($parentID) ) {
        $parentID = implode(',', $parentID);
        $sql .= "parent_id in ({$parentID})";
    }
    else {
        $sql .= "parent_id = {$parentID}";
    }

    $tree = array();
    $idList = array();

    $res = mysql_query($sql);
    while ( $row = mysql_fetch_assoc($res) ) {
        $row['children'] = array();
        $tree[$row['id']] = $row;
        $idList[] = $row['id'];
    }
    mysql_free_result($res);

    if ( $idList ) {
        $children = getTree($idList);
        foreach ( $children as $child ) {
            $tree[$child['parent_id']]['children'][] = $child;
        }
    }
    return $tree;
}

With provided sample data, it does at most 5 queries, when called as getTree(null) (for all entries):

select id, parent_id, title from category where parent_id is null
select id, parent_id, title from category where parent_id in (1,2,3)
select id, parent_id, title from category where parent_id in (4,5,6)
select id, parent_id, title from category where parent_id in (7,8)
select id, parent_id, title from category where parent_id in (9)

When called as getTree(4), 3 queries are performed:

select id, parent_id, title from category where parent_id = 4
select id, parent_id, title from category where parent_id in (7,8)
select id, parent_id, title from category where parent_id in (9)

Why the hell would you do that when you used those ip like titles you can simply use one where statement with like and get the result. — Steve Moretz, Aug 12 '21 at 12:59

Vikas Sharma · Answer 4 · 2017-06-02T18:10:58.453

<?php    
require('db/dbconnect.php');   

$user_id='triD-100';   
 $sql="select * from ajent_joining where sponser_id='".$user_id."'";   
 $qR=mysql_query($sql);   
 while($rowD=mysql_fetch_assoc($qR)){    
  echo $childId=$rowD["user_id"]; 
    echo "<br/>";  
  categoryChild($childId);    
   }   

  function categoryChild($childId) {   

    $s = "select user_id from ajent_joining where sponser_id='".$childId."'";
    $r = mysql_query($s);
    if(mysql_num_rows($r) > 0) {

       while($row = mysql_fetch_array($r)) {

          echo $childId=$row["user_id"];
          echo "<br/>";   
           categoryChild($childId);
    }
}

}


?>

score 1 · Answer 5 · edited May 16 '12 at 06:38

function categoryChild($id)
{
    $s = "SELECT category_id,name FROM proads_categories WHERE parent_id =".$id;    
    $r = mysql_query($s);
    $children = array();
    if(mysql_num_rows($r) > 0)
    {
        #It has children, let's get them.
        while($row = mysql_fetch_array($r))
        {          
            #Add the child to the list of children, and get its subchildren
            $children[$row['category_id']]['nam'] = $row['name'];
            $arr = categoryChild($row['category_id']);
            if(count($arr) > 0)
            {
                $children[$row['category_id']]['child'] = categoryChild($row['category_id']);
            } 
        }
    }
    return $children;
}

It's perfect. If you need please try this

score 1 · Answer 6 · edited May 16 '12 at 06:38

function breadCrumb($id)
{
    $ar = array();
    $result = mysql_query("SELECT * FROM groups WHERE ParentID = '$id'");
    if(mysql_num_rows($result) > 0)
    {
        while($row = mysql_fetch_object($result))
        {
            $ar[] = $row->DBGroupID;
            $r = mysql_query("SELECT * FROM groups WHERE ParentID = '".$row->GroupID."'");
            if(mysql_num_rows($r) > 0)
                $ar = array_merge($ar, breadCrumb($row->GroupID, 1));
        }
    }
    return $ar;
}

score 0 · Answer 7 · answered Jan 27 '15 at 09:35

Using Prestashop function :

public function getRecursiveChildren() {

    $subCategories = $this->recurseLiteCategTree();
    //print_r($subCategories);


    $my_tab = array();

    foreach ($subCategories['children'] as $subc) {
        $my_tab[]   = $subc['id'];
        foreach ($subc['children'] as $subc2) {
            $my_tab[]   = $subc2['id'];
            foreach ($subc2['children'] as $subc3) {
                $my_tab[]   = $subc3['id'];
                foreach ($subc3['children'] as $subc4) {
                    $my_tab[]   = $subc4['id'];
                }
            }
        }
    }
    $my_tab [] = $this->id; 

    return $my_tab;
}

this can be ameliorated using recursivity but no time for that today :'(

recursive function to get all the child categories

7 Answers7

Linked