parent-child tree

[P3t3r]

Ok, the problem is the following:

I have a table (in an MySQL database), say 'forums' with columns 'forum_id', 'parent_forum_id'.

 

Now I'd want to reconstruct a tree from this: if the entries are, say:

(1,0),(2,0),(3,2),(4,3),(5,3),(6,2),(7,6),(8,6),(9,2),(10,0)

then this represents a tree:

0 (root)
-1
-2
  --3
    ---4
    ---5
  --6
    ---7
    ---8
  --9
-10

(since 1 is a child of 0, 2 is a child of 0, 3 is a child of 2, etc.)

 

There will be no more than three levels, if that makes it easier.

Now, I'd want to return this in the following format (with the three levels being chapter, section and subsection):

 \chapter{1} Text 1.
\chapter{2} Text 2.
\section{3} Text 3.
\subsection{4} Text 4.
\subsection{5} Text 5.
\section{6} Text 6.
\subsection{7} Text 7.
\subsection{8} Text 8.
\section{9} Text 9.
\chapter{10} Text 10.

 

How do I do this properly (without using dozens of sql queries)?

[JonnyThunder]

You'd be best using a recursive function (a function which calls itself)

 

Here is a small portion of a class I wrote to control a category tree.  Same format (ID / parent).  May give you a pointer....

 

        function array_get_tree ($parentid) {

            // Cycle through all array data
            foreach($this->catdata as $current) {

                // Grab the current ID / parent ID
                $c_id   = $current[$this->id_field];
                $c_pid  = $current[$this->parentid_field];

                // If the parent ID matches
                if ($c_pid == $parentid) {

                    // Add an 'indent' field so we can display a tree if needed
                    $current['indent'] = $this->indentpointer;

                    // Add this record to the data subset array
                    $this->catdatasub[] = $current;

                    // Virtual indent
                    $this->indentpointer++;

                    // Display subtree for this item (call this function with the new parent ID)
                    $this->array_get_tree($c_id);

                    // Virtual outdent
                    $this->indentpointer--;

                }

            }

        }

 

Hope this helps!

 

EDIT:  Just to clarify - all this function does is display the category tree.  Keep in mind it's part of a bigger class, which is why i've got loads of "$this->variable" type use.  Essentially though, you'd roughly use the same method to do anything with a tree format.  Also, in the example above - I read the whole category data table into an array - which is why there are no references to the database.

[P3t3r]

Hmm... looks interesting, but I don't really understand how I have to adjust my sql queries to this? Or just read them all and then construct the whole tree (somehow the code makes me think you only construct the child tree of a given parent)?

 

Do you know any small example script making use of this?

[JonnyThunder]

OK, i've just written this as a working example of using the recursive function to generate a tree of IDs and Parents.  Will work the same for a database but im using an array of test data to mess about with.

 

<?php



class example
{

	var $catdata        = array();    // array to store original untouched category data
	var $catdatasub     = array();    // array to store subset of category data
	var $id_field       = "ID";       // the ID field name for the category data
	var $parentid_field = "PARENTID"; // parent ID field name for the category data


	function array_get_tree ($parentid) {

		// Cycle through all array data
		foreach($this->catdata as $current)
		{
			// Grab the current ID / parent ID
			$c_id   = $current[$this->id_field];
			$c_pid  = $current[$this->parentid_field];

			// If the parent ID matches
			if ($c_pid == $parentid)
			{
				// Add an 'indent' field so we can display a tree if needed
				$current['indent'] = $this->indentpointer;

				// Add this record to the data subset array
				$this->catdatasub[] = $current;

				// Virtual indent
				$this->indentpointer++;

				// Display subtree for this item (call this function with the new parent ID)
				$this->array_get_tree($c_id);

				// Virtual outdent
				$this->indentpointer--;
			}
		}
	}

}


// Test Data
$mydata[] = array("ID"=>"1", "PARENTID"=>"0");
$mydata[] = array("ID"=>"2", "PARENTID"=>"0");
$mydata[] = array("ID"=>"3", "PARENTID"=>"2");
$mydata[] = array("ID"=>"4", "PARENTID"=>"2");
$mydata[] = array("ID"=>"5", "PARENTID"=>"0");
$mydata[] = array("ID"=>"6", "PARENTID"=>"4");


// instantiate the class
$test  = new example();


// Stuff the data array with your data
// (im gonna just copy my array - but you'd create an array from DB and use it)
$test -> catdata = $mydata;


// Get the tree, starting from parent 0 (root)
$test -> array_get_tree(0);


// Show the output array
//print "<pre>";
//print_r($test->catdatasub);
//print "</pre>";


// And display how it would look
foreach($test->catdatasub as $current)
{
	print str_repeat("- ", $current['indent']) . $current['ID'] . "<br/>\n";
}

?>