优化PHP代码以从大型数据集构建树结构

Question

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.

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.

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

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

Here is my code:

<?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 => $row)
    {
        if($row['parent_id'] == $parent_id)
        {
            foreach($row as $k => $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 => $v)
    {
        if($parentfound || $k == $parent_id)
        {
            $rowdata = array();
            foreach($v as $field => $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 '<pre>';
$result_data = fetch_recursive(buildtree($data),(int)$value['id']);
print_r($result_data);
echo '</pre>';
  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());
     }
    }
}

?>

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

Original Problem Statement

I have the following Data in My DB:

Manager_ID Employee_ID

AAA   BBB
AAA   CCC
AAA   DDD
BBB   EEE
BBB   FFF
CCC   GGG
FFF   HHH
III   JJJ
JJJ   KKK
JJJ   LLL

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:

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

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.

Answer 1

Rule of thumb: PHP has 40 bytes of overhead for each scalar value. That adds up fast.

Don't use the deprecated mysql_* interface; use either mysqli_* or PDO.

Solution to your problem: Put all the data into temporary MySQL tables, then use SQL statements to rearrange into the hierarchical format. Ah; you seem to have the data already in a table.

The SQL would involve one SQL statement per level of the hierarchy, probably doing all records at that level in a single statement. PHP code would control the levels, since MySQL has no concept of hierarchy.

Quite possibly this change will even run faster that PHP would have -- you end up with a small number of queries, instead of the 20000 (or more) you currently are designed for.

Here's a way to transform your code. Start with the innermost for loop. Think about how that loop can be turned into one SQL statement. Then do it. (You may be happy enough after this one step.)