Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a table

enter image description here

Which I want show recursively like below picture

enter image description here

I am using a recursive function in php

function  reccall($cat_id)
 {
   global $no,$recArray;
   $sql = "SELECT a.*
      FROM cat_master   
           WHERE 
        parent_id = $cat_id 
      ORDER BY 
        id ASC
      ";
         $result = mysql_query($sql) or die("Could not fetech Recursively");
    while($row = mysql_fetch_object($result))
    {
      $recArray[$no]['value'] = mysql_real_escape_string($row->value);
      $recArray[$no]['id'] = $row->id;
       ++$no;
      reccall($row->id);
    }
    return $recArray;    
 }

but I am not able to generate a structured array like how the order is not the picture. A simple array is created all the time. Can anyone help me with creating the structured array like the order shown above.

share|improve this question
You might like Managing Hierarchical Data in MySQL by Mike Hillyer – Treffynnon May 24 '12 at 11:46
You should be using Closure Tables instead .. or at least: read SQL Antipatterns book. – tereško May 24 '12 at 11:48
2  
Also .. stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process . Instead you should learn about prepared statements and use either PDO or MySQLi. If you cannot decide, this article will help to choose. If you care to learn, here is a quite good PDO-related tutorial. – tereško May 24 '12 at 11:48
Yes I like but I need to fetch value recursively so that example is not given here – Jhilom May 24 '12 at 11:50

2 Answers

up vote 1 down vote 
<?
// I identified this function separately because it is performed only once, for preparing data
// It's collect an array of all parents in the correct order for each id
function dest($array) {
    foreach($array as $key=>$value) {
        if($value['pid']==0) continue;

        $pid = $key;
        $array[$key]['dest'] = array();
        while ( $pid = $array[$pid]['pid'] ) {
            if($key == $pid) exit("this tree is broken");
            $array[$key]['dest'][] = $pid;
        }
    }

    return $array;
}
// Recursive function that puts the items in the correct tree. removes the parameter dest.
function tree($array) {
    foreach($array as $key=>$value) {
        if( is_array($value['dest']) && !empty($value['dest']) ) {
            $pid = array_pop($value['dest']);
            if( empty($value['dest']) ) unset($value['dest']);

            $array[$pid]['childrens'][$key] = $value;
            $array[$pid]['childrens'] = tree($array[$pid]['childrens']);
            unset($array[$key]);
        }
    }

    return $array;
}

$array = array(
    1 => array(
        'title'=>'q',
        'pid'=>0,
        ),
    2 => array(
        'title'=>'w',
        'pid'=>1,
        ),
    3 => array(
        'title'=>'e',
        'pid'=>0,
        ),
    4 => array(
        'title'=>'r',
        'pid'=>2,
        ),
    5 => array(
        'title'=>'t',
        'pid'=>1,
        ),
);

$tree = tree( dest($array) );

echo '<pre>';
print_r($array);
print_r($tree);
?>

By the way, I should note that these arrays are not very useful. Better to use the result of the function dest().

share|improve this answer
up vote 1 down vote accepted

use this fucntion instead of your function and your problem will be solved I hope

function reccall($cat_id)
 {
     $sql = "SELECT a.*
      FROM cat_master   
           WHERE 
        parent_id = $cat_id 
      ORDER BY 
        id ASC
      ";
         $result = mysql_query($sql) or die("Could not fetech Recursively");
    while($row = mysql_fetch_object($result))
    {
      $recArray[$no]['main']['value'] = mysql_real_escape_string($row->value);
      $recArray[$no]['main']['id'] = $row->id;         
      $recArray[$no]['child'] = reccall($row->id);
       ++$no;
    }
    return $recArray;    
 }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.