phprecursiontreewhile-loop

how to loop over looped elements?

I'm creating a tree-structure of categories with parentid's which can be called from children in such a way:

ID | Name | ParentID
1    1      0
2    2      1
3    3      2
4    4      1

Resulting in this:

1 = 1
2 = 1 -> 2
3 = 1 -> 2 -> 3
4 = 1 -> 4

which means 3 is a child of 2, which is a child of 1.

when trying to get this idea (with the -> to show what relations are set) I only get to the second grade (1 -> 2) but not to the third (1->2->3) because of the looping function I use for it.

//put all ID's in an array
while ($row2 = $connector->fetchArray($result2)){
 $id = $row2['ID'];
 $parents[$id] = $row2['name'];
}

// show the tree-structure
while ($row = $connector->fetchArray($result)){
    if($row['parentid']!=0)echo $parents[$row['parentid']].' -> ';
    echo $row['name'].' -    ';
    echo '<br>';
}

I'd like two things to change:

  1. have the code automatically generate a tree sized as necessary.
  2. in the while-loops i have to select the $result twice (once as $result, once as $result2) to make it work. these $result's have exactly the same database-query:
    SELECT ID,name,parentid FROM categories

to fetch results from. I'd like to only declare this once.



Thanks for all the good answers. I've gone with the easiest, less-code-to-implement approach:

$result = $connector->query('SELECT ID,name,parentid FROM categories');

// Get an array containing the results.
$parents = array();
while ($row = $connector->fetchArray($result)){
  $id = $row['ID'];
  $parents[$id] = array('ID' => $row['ID'],'name' => $row['name'],'parentid' => $row['parentid']);
}

foreach ($parents as $id => $row){
  $pid=$id;
  $arrTmp= array();
  do {      // iterate through all parents until top is reached
    $arrTmp[]=$pid;
    $pid = $parents[$pid]['parentid'];
  }while ($pid != 0);
    $arrTmp = array_reverse($arrTmp);
  foreach($arrTmp as $id){
    echo $parents[$id]['name'].' -&gt; ';
    }
  echo '<br>';
}
Solution

  • If you really want to do hierachies with parent ids(suitable only for small number of items/hierachies)

    I modified your code a little bit(I did not test it so there may be some syntax errors):

    //put all recordsets in an array to save second query
while ($row2 = $connector->fetchArray($result2)){
  $id = $row2['ID'];
  $parents[$id] = array('name' => $row2['name'],'parent' => $row2['parentid']);
}

// show the tree-structure
foreach ($parents as $id => $row){
  $pid = $row['parentid'];
  while ($pid != 0){      // iterate through all parents until top is reached
    echo $parents[$pid]['name'].' -&gt; ';
    $pid = $parents[$pid]['parentid'];
  }
  echo $parents[$id]['name'].' -    ';
  echo '<br>';
}

    To answer your comment:

    $parents = array();
$parents[2] = array('ID'=>2,'name'=>'General','parentid'=>0); 
$parents[3] = array('ID'=>3,'name'=>'Gadgets','parentid'=>2); 
$parents[4] = array('ID'=>4,'name'=>'iPhone','parentid'=>3); 

foreach ($parents as $id => $row){
  $pid=$id;
  $arrTmp= array();
  do {      // iterate through all parents until top is reached
    $arrTmp[]=$pid;
    $pid = $parents[$pid]['parentid'];
  }while ($pid != 0);
    $arrTmp = array_reverse($arrTmp);
  foreach($arrTmp as $id){
    echo $parents[$id]['name'].' -&gt; ';
    }
  echo '<br>';
}

    Prints out:

    General ->

    General -> Gadgets ->

    General -> Gadgets -> iPhone ->