title: How can I generate a tree structure from a table in a database?
body: <p>I'm trying to generate a tree structure from a table in a database. The table is stored flat, with each record either having a parent_id or 0. The ultimate goal is to have a select box generated, and an array of nodes.</p>
  
  <p>The code I have so far is :</p>
  
  <pre><code>function init($table, $parent_id = 0) 
  {
  
      $sql = "SELECT id, {$this-&gt;parent_id_field}, {$this-&gt;name_field} FROM $table WHERE {$this-&gt;parent_id_field}=$parent_id ORDER BY display_order";
  
      $result = mysql_query($sql);
  
      $this-&gt;get_tree($result, 0);
  
      print_r($this-&gt;nodes);
      print_r($this-&gt;select);
      exit;
  }
  
  function get_tree($query, $depth = 0, $parent_obj = null)
  {   
      while($row = mysql_fetch_object($query))
      {   
          /* Get node */
          $this-&gt;nodes[$row-&gt;parent_category_id][$row-&gt;id] = $row;
  
          /* Get select item */
          $text = "";
          if($row-&gt;parent_category_id != 0) {
              $text .= "    ";
          }
          $text .= "$row-&gt;name";
          $this-&gt;select[$row-&gt;id] = $text;
  
          echo "$depth $text\n";
  
          $sql = "SELECT id, parent_category_id, name FROM product_categories WHERE parent_category_id=".$row-&gt;id." ORDER BY display_order";
  
          $nextQuery = mysql_query($sql);
          $rows = mysql_num_rows($nextQuery);
  
          if($rows &gt; 0) {
              $this-&gt;get_tree($nextQuery, ++$depth, $row);
          }            
      }
  }
  </code></pre>
  
  <p>It's almost working, but not quite. Can anybody help me finish it off?</p>