How to build a tree view with PHP / SQL?

Question

What's the best way to:

1. Get the data from the db using a single query
2. Loop through the results building e.g. a nested unordered list

My table has id, name and parent_id columns.

---

Here's an update to my last answer, with a counter that gives each ul a nesting 'level' class, and some comments.

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?

<?

//
// 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' => $row['id'], 
    'description' => $row['description'], 
    'parent_id' => $row['parent_id']
  );
}


// print '<pre>';
// 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 => $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] => 0   == $parent = 0 (true)
    // eg. [parent_id] => 20  == $parent = 0 (false)
    //
    if ($value['parent_id'] == $parent) 
    {                   

      //
      // Only print the wrapper ('<ul>') 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 '<ul class="level-' . $level . '">';

        $level++;
      }

      //
      // Print the list item
      //
      echo '<li><a href="?id=' . $value['id'] . '">' . $value['description'] . '</a>';

      //
      // 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 '</li>';


    }

  }

  //
  // If we opened the wrapper above, close it.
  //
  if ($has_children === true) echo '</ul>';


}

// ----------------------------------------------------------------

//
// generate list
//
generate_tree_list($categories);


?>

Answer 1

You can create a breadcrumb view style by using arrays, without using a recursive function.

Here is my working code:

First, make a SQL query like this:

$category = CHtml::listData(TblCategory::model()->findAllCategory(array(
'distinct'=>true,
'join'=>'LEFT JOIN tbl_category b on b.id = t.cat_parent',
'join'=>'LEFT JOIN tbl_category c on c.cat_parent = 0',
'order' => 'cat_name')),'id','cat_name');

I am using yii related code so you can use normal join queries, then form an array in a foreach() function

public function findAllCategory($condition='',$params=array())
{
    
    Yii::trace(get_class($this).'.findAll()','system.db.ar.CActiveRecord');
    $criteria=$this->getCommandBuilder()->createCriteria($condition,$params); 
    
    $category = array();
    $cat_before;
    $parent_id = array();
    $cat_before = $this->query($criteria,true); 
    
    //echo "<br><br><br><br><br><br><br>";
    
    foreach($cat_before as $key => $val)
    {
        $category[$key] = $val;
        $parent_id[$key]['cat_parent'] =$val['cat_parent'];
        $parent_id[$key]['cat_name'] =$val['cat_name']; 
        
        foreach($parent_id as $key_1=> $val_1)
        {   
            
            if($parent_id[$key]['cat_parent'] == $category[$key_1]['id'])
            {
                $category[$key]['cat_name']= $category[$key_1]['cat_name'] .' > '.  $parent_id[$key]['cat_name'];
                
            }
        }
    } 
    return $cat_before;  
}

Then you can get result using Main cat >> subcat 1 >> subcat_1 inner >> ...

Answer 2

function generate_list($array,$parent,$level)
{

  foreach ($array as $value)
  {
    $has_children=false;

    if ($value['parent_id']==$parent)
    {

      if ($has_children==false)
      {
        $has_children=true;
        echo '<ul>';
      }

      echo '<li>'.$value['member_name'].' -- '.$value['id'].' -- '.$value['parent_id'];

      generate_list($array,$value['id'],$level);

      echo '</li>';
    }

    if ($has_children==true) echo '</ul>';

    echo $value['parent_id'];
  }

}

Answer 3

MySQL have created a good article on this subject: Managing Hierarchical Data in MySQL