title: How to build a tree view with PHP / SQL?
body: <p>What's the best way to:</p>
  
  <ol>
  <li>Get the data from the db using a single query</li>
  <li>Loop through the results building e.g. a nested unordered list</li>
  </ol>
  
  <p>My table has <code>id</code>, <code>name</code> and <code>parent_id</code> columns.</p>
  
  <hr>
  
  <p>Here's an update to my last answer, with a counter that gives each ul a nesting 'level' class, and some comments.</p>
  
  <p>Could anyone suggest how to adapt this to use table rows, without nesting, but with some kind of class numbering hierarchy for css/js hooks?</p>
  
  <pre><code>&lt;?
  
  //
  // Get the data
  //
  include_once("inc/config.php");
  
  $query = "SELECT c.* 
            FROM categories AS c
            ORDER BY c.id
            LIMIT 1000";          
  
  $result = pg_query($db, $query);
  
  //
  // Load all the results into the row array
  //
  while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC))
  {
    //
    // Wrap the row array in a parent array, using the id as they key
    // Load the row values into the new parent array
    //
    $categories[$row['id']] = array(
      'id' =&gt; $row['id'], 
      'description' =&gt; $row['description'], 
      'parent_id' =&gt; $row['parent_id']
    );
  }
  
  
  // print '&lt;pre&gt;';
  // print_r($category_array);
  
  // ----------------------------------------------------------------
  
  //
  // Create a function to generate a nested view of an array (looping through each array item)
  // From: http://68kb.googlecode.com/svn-history/r172/trunk/upload/includes/application/controllers/admin/utility.php
  //
  function generate_tree_list($array, $parent = 0, $level = 0)
  {
  
    //
    // Reset the flag each time the function is called
    //
    $has_children = false;
  
    //
    // Loop through each item of the list array
    //
    foreach($array as $key =&gt; $value)
    {
      //
      // For the first run, get the first item with a parent_id of 0 (= root category)
      // (or whatever id is passed to the function)
      //
      // For every subsequent run, look for items with a parent_id matching the current item's key (id)
      // (eg. get all items with a parent_id of 2)
      //
      // This will return false (stop) when it find no more matching items/children
      //
      // If this array item's parent_id value is the same as that passed to the function
      // eg. [parent_id] =&gt; 0   == $parent = 0 (true)
      // eg. [parent_id] =&gt; 20  == $parent = 0 (false)
      //
      if ($value['parent_id'] == $parent) 
      {                   
  
        //
        // Only print the wrapper ('&lt;ul&gt;') if this is the first child (otherwise just print the item)      
        // Will be false each time the function is called again
        //
        if ($has_children === false)
        {
          //
          // Switch the flag, start the list wrapper, increase the level count
          //
          $has_children = true;  
  
          echo '&lt;ul class="level-' . $level . '"&gt;';
  
          $level++;
        }
  
        //
        // Print the list item
        //
        echo '&lt;li&gt;&lt;a href="?id=' . $value['id'] . '"&gt;' . $value['description'] . '&lt;/a&gt;';
  
        //
        // Repeat function, using the current item's key (id) as the parent_id argument
        // Gives us a nested list of subcategories
        //
        generate_tree_list($array, $key, $level); 
  
        //
        // Close the item
        //
        echo '&lt;/li&gt;';
  
  
      }
  
    }
  
    //
    // If we opened the wrapper above, close it.
    //
    if ($has_children === true) echo '&lt;/ul&gt;';
  
  
  }
  
  // ----------------------------------------------------------------
  
  //
  // generate list
  //
  generate_tree_list($categories);
  
  
  ?&gt;
  </code></pre>