loop though a multi level array with an unknown amount of levels

Question

I have a categories table that looks like this:

----------------------------------------
|  id   |  parentId  |  Name           |
----------------------------------------
   1          0         Cat 1
   2          0         Cat 2
   3          0         Cat 3
   4          2         Cat 4
   5          3         Cat 5
   6          5         Cat 6

Basically I need to iterate through the categorys creating a UL LI html list like the following:

<ul id="categories">
    <li id="1">Cat 1</li>
    <li id="2">Cat 2
       <ul>
           <li id="4">Cat 4</li>
       </ul>
    </li>
    <li id="3">Cat 3
        <ul>
           <li id="5">Cat 5
               <ul>
                   <li id="6">Cat 6</li>
               </ul>
           </li>
        </ul>
    </li>
</ul>

Im having major issues trying to iterate over this trying to create the above html. The id's maybe any number of levels deep within parentId's. Im donig this in PHP. Because there are nth number of levels deep I think I need to do some kind of array_walk funuction but not surch how. Also to make things just a little harder the machine it sists on runs PHP4 and I know it needs upgrading but it cant at the min so I need a php 4 solution ideally. How should I go about doing this?

yes it start form a mysql table which is pushed into a categories class/object however if I can do this at the SQL end then great — azzy81, Feb 21 '12 at 11:39
possible duplicate: http://stackoverflow.com/questions/8847365/convert-my-database-table-to-a-tree-and-get-leaf-nodes-in-php and http://stackoverflow.com/questions/7649803/collapsible-dynamic-bom-via-jquery — Yoshi, Feb 21 '12 at 11:43

score 2 · Accepted Answer · answered Feb 21 '12 at 11:41

2

Try the left/right tree method for storing hierarchical information in a database.

http://blogs.sitepoint.com/hierarchical-data-database/

This is what I do in my website, where I have multi-level LIs that need to open at 1:6 and have children 2:3,4:5 where the first number is the 'left' and the second is the 'right'. I have about 5 levels deep at the moment, but you could have many more. It's just a matter of developing an interface for setting the correct left/right values based on the position you add it to.

You just have to add a 'lft' and 'rgt' column to your table (as explained in that article).

enter image description here

answered Feb 21 '12 at 11:41

Benno

3,008
3
26
41

IM having a read now m8 thanks. I'll let you know how I get on ^^ – azzy81 Feb 21 '12 at 11:47
* Sorry, just realised there are two methods on that blog post. The first page has the adjacency model (which is what you're trying at the moment I think). The second/third pages are left/right tree method, which is the slightly more complex one, but in my experience, far more extensible. – Benno Feb 21 '12 at 11:53
1

I really like this nested set solution and Id never heard of it. I didn;t actually use it as my solution in the end however I feel of all the solutions this is the nicest and most extensible way to go so Im markign this as my accepted answer. Thanks to everyone that replied though...your all legends ^^ – azzy81 Feb 22 '12 at 16:34
Just for people interested how I achieved my solution I managed to get my objected Orientated head back on and worked out I could pull all child categories out while loading any category. My object now has a children array and if the parent Id is > 0 I know it has a parent so I force the object back on itself. As soon as I had my object correct it was a very simple function to wrap the ul/li tags around in the right places. – azzy81 Feb 22 '12 at 16:35

score 0 · Answer 2 · answered Feb 21 '12 at 11:41

First create a tree structure and insert your categories into the tree using id and parent_id. Then try Depth-first_search using either a list of references to arrays to be processed or recursion.

function printRecList($tree){
   // exit condition
   if (is_string($tree))
       echo "<li>$tree</li>";

   echo "<ul>";
   foreach ($tree as $subtree)
       printRecList($subtree); // recursion step
   echo "</ul>";
}

score 0 · Answer 3 · answered Feb 21 '12 at 11:43

The way your database is structured, you cannot do that with a single mysql query and you should do it recursively. Something in line with:

function print_children ($id) {
    $children = query("SELECT * FROM `table` WHERE `parentId` = " . (int)$id);
    if (!empty($children)) {
         echo '<ul>';
         foreach ($children as $child) {
              echo '<li>' . $child['name'];
              print_children($child['id']);
              echo '</li>';
         }     
         echo '</ul>';
    }
}

print_children(0);

Replace query with something that gets results for your database query.

score 0 · Answer 4 · answered Feb 21 '12 at 11:54

function writelevel($id, $txt, $children) {
  if (isset($txt[$id]))
    echo "<li id=\"$id\">".$txt[$id];
  if (isset($children[$id])) {
    echo "<ul>";
    foreach ($children[$id] as $child)
      writelevel($child, $txt, $children);
    echo "</ul>";
  }
  if (isset($txt[$id]))
    echo "</li>";
}

//Assuming your query is done and the result is in $qry

$txt=array();
$children=array();


//Fetch and structure data
while (true) {
  //Fetch next row
  $row=mysql_fetch_row($qry);
  if (!$row) break;

  //Store text
  $txt[$row[0]]=$row[2];

  //Store child relationships
  if (!isset($children[$row[1]])) $children[$row[1]]=array();
  $children[$row[1]]=$row[0];
}

//Writeout
writelevel(0);

loop though a multi level array with an unknown amount of levels

4 Answers4