Optimize PHP code to build Tree structure out of large dataset
<p>I have a program to populate highest level node or parent node to be populated next to each of the child and grand child nodes.</p>

<p>I have first made a tree structure and then parse through to populate highest level node or root node next to each child/grandchild nodes.</p>

<p>But when I run the program on a large data set >20000 rows i get this error:</p>

<pre><code>Fatal error:  Out of memory (allocated 1807745024) (tried to allocate 36 bytes) in C:\xampp\htdocs\test_project\index.php on line 20
</code></pre>

<p>Here is my code:</p>

<pre><code>&lt;?php

include('mysql_config.php');

set_time_limit(0);
ini_set('memory_limit', '2048M');
$r = mysql_query("SELECT Emp_ID AS id,fname AS name,Manager_ID AS parent_id FROM targets");
        $data = array();
        while($row = mysql_fetch_assoc($r)) {
         $data[] = $row;
         }    
$j = mysql_query("SELECT Emp_ID AS id,fname AS name,Manager_ID AS parent_id FROM targets where Type = 'Super Manager'");
        $parent_data = array();
        while($row = mysql_fetch_assoc($j)) {
         $parent_data[] = $row;
         }           

function buildtree($src_arr, $parent_id = 0, $tree = array())
{
    foreach($src_arr as $idx =&gt; $row)
    {
        if($row['parent_id'] == $parent_id)
        {
            foreach($row as $k =&gt; $v)
                $tree[$row['id']][$k] = $v;
            unset($src_arr[$idx]);
            $tree[$row['id']]['children'] = buildtree($src_arr, $row['id']);
        }
    }
    ksort($tree);
    return $tree;
}

function fetch_recursive($tree, $parent_id, $parentfound = false, $list = array())
{
    foreach($tree as $k =&gt; $v)
    {
        if($parentfound || $k == $parent_id)
        {
            $rowdata = array();
            foreach($v as $field =&gt; $value)
                if($field != 'children')
                    $rowdata[$field] = $value;
            $list[] = $rowdata;
            if($v['children'])
                $list = array_merge($list, fetch_recursive($v['children'], $parent_id, true));
        }
        elseif($v['children'])
            $list = array_merge($list, fetch_recursive($v['children'], $parent_id));
    }
    return $list;
}
foreach($parent_data as $value)
{ 
echo '&lt;pre&gt;';
$result_data = fetch_recursive(buildtree($data),(int)$value['id']);
print_r($result_data);
echo '&lt;/pre&gt;';
  if(!empty($result_data)){
   foreach($result_data as $child_val){
     $su_id=(int)$value['id'];
     $name_man=(string)$value['name'];
     $dest_id=$child_val['id'];
       mysql_query("update targets set SM_ID ='$su_id',SM_Name='$name_man' where Emp_ID='$dest_id'") or die (mysql_error());
     }
    }
}

?&gt;
</code></pre>

<p>How can i optimize the code to solve this error. I tried this code with 100 rows and it worked fine.</p>

<p><strong>Original Problem Statement</strong></p>

<p>I have the following Data in My DB:</p>

<pre><code>Manager_ID Employee_ID

AAA   BBB
AAA   CCC
AAA   DDD
BBB   EEE
BBB   FFF
CCC   GGG
FFF   HHH
III   JJJ
JJJ   KKK
JJJ   LLL
</code></pre>

<p>I wish to populate the child nodes with their respective highest level roots nodes such that all child nodes have a root level data/parent mapped to them something like this:</p>

<pre><code>Employee_ID 1st Level Node
AAA         Root
BBB         AAA
CCC         AAA
DDD         AAA
EEE         AAA
FFF         AAA
GGG         AAA
HHH         AAA
III         Root
JJJ         III
KKK         III
LLL         III
</code></pre>

<p>I have tried creating a PHP function to create a tree but am unable to take it from there to populate the last or highest level root to the respective child nodes.</p>