recursive function to get all the child categories

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']);
    }
    el开发者_StackOverflowse {
        $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

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.

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;
}
?>

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)

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

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;
}

<?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);
    }
}

}


?>

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 :'(