Generating a tree-view from a single DB Table

Foo

I'm using a single table, Category , which contains the fields:

ID (Primary Key)
Parent (Integer references Category.ID)
Description

As you can see, the table loops back on itself to generate a big tree of categories and subcategories. It's quite flexible but my problem comes when I want to represent this, visually.

I dont even know where to begin. How can I use some loops in PHP to run through the category table and print out a visual representation of its structure?

So far, I think I need to do it using a number of nested arrays and queries, but since the depth of the tree depends entirely on how the user has added categories, I dont quite know how I would know when I had finished generating the tree, or stopping myself going over the same categories more than once.

Eeek.

Any help, much appreciated 😃

laserlight

hehe, this sounds like a fun script, though you'll probably have to be careful that the categories are not in a circular hierarchy.

Let's try creating a category tree node class:

class CatTreeNode {
	var $ID;
	var $Parent;
	var $Description;
	var $subcats;

function CatTreeNode($ID, $Parent, $Description) {
	$this->ID = $ID;
	$this->Parent = $Parent;
	$this->Description = $Description;
	$this->subcats = array();
}

//find the subcategories of this category
function findSubCats(&$catnodes) {
	$num = 0; //total number of child nodes
	//loop through array of uncollected nodes
	foreach ($catnodes as $cat) {
		if ($cat->getID() == $this->ID) {
			$this->subcats[] = $cat;
			unset($catnodes[$this->ID]);
			$num++;
		}
	}
	//recursively apply to child nodes
	for ($i = 0; $i < $num; $i++) {
		$this->subcats[$i]->findSubCats($catnodes);
	}
}

function getID() {
	return $this->ID;
}

function getDescription() {
	return $this->Description;
}

function getSubCats() {
	return $this->subcats;
}
}

Once we use the findSubCats() function on an array of CatTreeNode objects, this node would store its child nodes, which would in turn find their own child nodes.

Now we have to deal with getting the array of nodes to begin with, and finding the top level nodes:

class CatTree {
	var $cats;

function CatTree() {
	//array of uncollected nodes
	$catnodes = array();
	$num = 0; //total number of top level nodes
	//extract the whole Category table
	$query = mysql_query("SELECT * FROM Category");
	//loop through each category entry
	while ($row = mysql_fetch_assoc($query)) {
		//blank IDs mean top level nodes
		if ($row['ID'] == '') {
			$this->cats[] = &new CatTreeNode(
				$row['ID'],
				$row['Parent'],
				$row['Description']
				);
			$num++;
		} else {
			$catnodes[] = &new CatTreeNode(
				$row['ID'],
				$row['Parent'],
				$row['Description']
				);
		}
	}
	//apply the subcategory find to all the top level nodes
	for ($i = 0; $i < $num; $i++) {
		$this->cats[$i]->findSubCats($catnodes);
	}
}

function getCats() {
	return $this->cats;
}
}

So to use it:

$nodetree = new CatTree;
$cats = $nodetree->getCats();

Now $cats contains the array of top level categories, from which all the various levels of subcategories can be obtained.

Of course, I havent tested this, so quite a bit of debugging, or even an entire redo, may be in order.

Foo

Holy cow, thanks!

Even if it's not workable, I'm sure I can refine and twist it now that you've given me the base to start with 😃

laserlight

Does it actually work?

Drakla

Hi - I've got a table structure exactly like that, basically a category tree. To display the entire tree padded out with links to various search pages I just use this:

table cats has id, pid (parent id) and name - all top level categories have a parent ID of zero.

function show_branch($catid=0)
{
	static $depth=-1;

require("adminwis/db-connect-inc.php");

$depth++;

$padder='';	
for($i=0;$i<$depth;$i++) $padder.="&nbsp;&nbsp;&nbsp;&nbsp;";

$query="SELECT * from cats WHERE pid=$catid ORDER BY name";	
$db->safe_query($query);

while( $row=$db->fetch_assoc() )
{
	$name="<a href='".SEARCHPAGE."?catid=$row[id]'>$row[name]</a>";
	echo $padder.$name.'<br>';

	show_branch( $row['id'] );
	//find mah childs
}

$db->free_result();
$db->close();

$depth--;
}

The witches code might be much better than this, though

Foo

Originally posted by laserlight
Does it actually work?

The parts I've leeched so far work just fine. I've had to adapt a little because I engineered the system with 1 special 'super root' element (which is always ID=1 in the table and is setup as part of my SQL create statements)..

I had 2 options (that I could see). Either the root categories had their parents set to their OWN IDs (and check for ($ID == $ParentID) for a root element, or setting the parent to a predefined arbitrary cutoff (like NULL or '0').

In the end I settled with '1' as the terminator. The advantage is that I can use the root element as the front page for the whole system.

THanks for the codes, both! Very useful!

Foo

So a quick follow-up:

I've decided I should code a proper class around my Category object. This is the code I have right now and it works (yay!):

Class Category
    {
    	var $ID, $Name, $Description, $Parent, $Colour;

	Function Category( $ID )
	{
		$CategoryDB = New Database( "localhost" , "***" , "***" , "***" );
		$CategoryDB->Query( "SELECT * FROM Category WHERE ID='$ID'" );
		if ( $CategoryDB->NumRows() ) {
    		$DBRow = $CategoryDB->GetRow();
    		$this->ID = $ID;
    		$this->Name = $DBRow['Name'];
    		$this->Parent = $DBRow['Parent'];
    		$this->Description = $DBRow['Description'];
    		$this->Colour = $DBRow['Colour'];

    		//Generate array of child categories
    		$CategoryDB->Query( "SELECT ID FROM Category WHERE Parent = '$ID'" );
    		while ( $DBRow = $CategoryDB->GetRow() )
    		{
                $this->Children[] = $DBRow['ID'];
    		}

    		//Generate array of files
    		$CategoryDB->Query( "SELECT ID FROM File WHERE Category='$ID'" );
    		while ( $DBRow = $CategoryDB->GetRow() )
    		{
                $this->Files[] = New File( $DBRow['ID'] );
    		}

        } else {
	        die ("FATAL CLASS ERROR: That category does not exist in the database");
        }
	}

Now, I'm thinking that if I tried to feed category object into the Children array, I'd end up creating a massive lump of category/file objects every time I created an instance of the category object.

Am I correct in this assumption? The way I see it, I have to only store the ID information for the child classes, not the entire object as I am doing with the Files.

(any ideas on how to better handle the DIE() statement would be greatly appreciated, also)

Man, I am so ad-libbing this entire project :o

el_quijote

Hi! I'm trying to create gui based on treeview. I've seen many examples that are beyond my comprenhension. I'm an experienced programmer but new to php environment. What part of the code shows visually the tree? Does it has the functionality of treeview control of vb6 (pardon for the reference). Up to now, this is the solution I think I can understand and implement.

el Quijote