ok well here is the solution to this i have made....

1) I don't know how to make SQL return the tree type relationship you want, and i don't think it can...

2) I decided to implement this in php with this badly named function

3) this function assumes input like this. each array item represents a row of the database, and each row is an array hashed with the column names. I user the PEAR DB object to pull out the table info like this: you could easily rewrite this function to use another form of input:

array(
array( 'id' => int, 'name' => string, 'parent' => int),
array( 'id' => int, 'name' => string, 'parent' => int),
array( 'id' => int, 'name' => string, 'parent' => int),
array( 'id' => int, 'name' => string, 'parent' => int)
)

5) the result is of a form which you can easily step through to generate a tree
$categories[ $id ] = array( 'name', 'parent', 'children', 'descendants', 'ancestors' );


6) you can easliy expand this function to track more than just the name.

7) you can easily modify this function to calculate info by stepping through once rather than saving all info as an array

<?php
function getCategoryHashesFromSqlResults( $results )
{
/// top category (not in database so we make it here)
$categories = array( NULL => array( "id" => NULL,
"name" => '_TOP_',
"parent" => NULL,
"children" => array(),
"descendents" => array(),
"ancestors" => array() ) );

foreach ( $results as $result ) {
$category_id = $result['id'];
$parent_id = $result['parent'];

if ( !isset($categories[$category_id]) ) {
$categories[$category_id] = array( "id" => $category_id,
"name" => $result['name'],
"parent" => NULL,
"children" => array(),
"descendants" => array(),
"ancestors" => array() );
} elseif ( !isset($categories[$category_id]['name']) ) {
$categories[$category_id]['name'] = $result['name'];
}

if ( !isset($categories[$parent]) ) {
$categories[$parent_id] = array( "id" => $parent_id,
"name" => NULL,
"parent" => NULL,
"children" => array(),
"descendants" => array(),
"ancestors" => array() );
}

/// add self to children list of parent
$categories[$parent_id]['children'][] = $category_id;

/// add parent to self as parent
$categories[$category_id]['parent'] = $parent_id;

}

while ( list($category_id,$category) = each($categories) ) {

if ( $category_id == NULL ) {
continue;
}

/// add self to descendant list of all ancestors
$parent_id = $categories[$category_id]['parent'];

while ( $parent_id !== NULL ) {
/// add self to descendant list of parent
$categories[$parent]['descendants'][] = $category_id;

/// add parent to ancestor list of self
$categories[$category_id]['ancestors'][] = $parent_id;

/// get next parent
$parent_id = $categories[$parent_id]['parent'];
}

$categories[NULL]['descendants'][] = $category_id;

}

return $categories;
}

?>