how to loop over looped elements?

Question

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'].' -> ';
    }
  echo '<br>';
}

Answer 1

Rather than have PHP organize the items into a tree, why not ask the database to do it for you? I found this article on hierarchical data to be very good and the examples are almost identical to yours.

---

EDIT

The SQL for getting the full tree using the Adjacency Model is not ideal. As the article explains it requires rather a lot of joins for even a small hierarchy. Is it not possible for you to use the Nested Set approach? The SQL stays the same regardless of the size of the hierarchy and INSERT and DELETE shouldn't be very difficult either.

Answer 2

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'].' -> ';
    $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'].' -> ';
    }
  echo '<br>';
}

Prints out:

General ->

General -> Gadgets ->

General -> Gadgets -> iPhone ->

Answer 3

Maybe easier with OOP. Just sort the query by parentId

Note: The listChildren method and the printout at the bottom is just there to show it is listed correctly. I did not interpret the question that the display was important.

class Element {
    public $id;
    public $name;
    public $parent = null;
    public $children = array();

    public function __construct($id, $name)
    {
     $this->id = $id;
     $this->name = $name;
    }

    public function addChild($element)
    {
     $this->children[$element->id] = $element;
     $element->setParent($this);
    }

    public function setParent($element)
    {
     $this->parent = $element;
    }

    public function hasChildren()
    {
     return !empty($this->children);
    }

    public function listChildren()
    {
     if (empty($this->children)) {
      return null;
     }

     $out = array();
     foreach ($this->children as $child) {
      $data = $child->id . ':' . $child->name;
      $subChildren = $child->listChildren();
      if ($subChildren !== null) {
       $data .= '[' . $subChildren . ']';
      }
      $out[] = $data;
     }
     return implode(',', $out);
    }
}

$elements = array();
$noParents = array();
while ($row = $connector->fetchArray($result)) {
    $elements[$row['id']] = $element = new Element($row['id'], $row['name']);

    if (isset($elements[$row['parent']])) {
     $elements[$row['parent']]->addChild($element);
    } else {
     $noParents[] = $element;
    }
}

foreach ($noParents as $element) {
    if ($element->hasChildren()) {
     echo "Element {$element->id} has children {$element->listChildren()}.\n";
    } else {
     echo "Element {$element->id} has no children.\n";
    }
}

Answer 4

If you are using PostgreSQL as the database, you can use the connectby() function to create the record set:

SELECT * 
FROM connectby('tableName', 'id', 'parent_id') 
    AS t(keyid text, parent_keyid text, level int);

I love this function, and use all the time in my code. It can do some very powerful things, very quickly, and you don't have maintain the left/right values like the (adjacency model).