Only return certain items in an tree array

[Jonob]

I have a mysql table that stores a parent-child relationhsip in a hierarchy  - this relationship may be one or many levels deep.

 

The structure of my table is:

user_provider_id: PK
provider_id: FK
parent_id: points to user_provider_id in the same table - this is what defines the parent-child relationship

 

The table is returned to php, which it is processed via an array into a tree structure. The function that I am using, which works perfectly, is as follows:

 

function provider_tree($provider_id)
{

	$sql = 
	"SELECT up.*
	FROM user_provider up
	WHERE provider_id = $provider_id";

	$list = do_array($sql);

	// Set up indexing of the above list (in case it wasn't indexed).
	$lookup = array();

	foreach($list as $item)
	{
		$lookup[$item['user_provider_id']] = $item;
	}

	// Now build tree.
	$tree = array();
	foreach($lookup as $id => $foo)
	{
		$item = &$lookup[$id];

		if($item['parent_id'] == 0)
		{
			$tree[$id] = &$item;

		}
		else
		if(isset($lookup[$item['parent_id']]))
		{
			$lookup[$item['parent_id']][$id] = &$item;
		}
		else
		{
			$tree['_orphans_'][$id] = &$item;
		}
	}
	return $tree;
}

 

As you can see, this will return all records (i.e. ALL user_provider_id) where provider_id = $provider_id.

 

What I would like to do is also limit the tree to a specific user_provider_id, and only have the tree for that user_provider_id and all of its children (and their children, etc). I will obviously have to pass in $provider_id and $user_provider_id to the function.

 

I could of course change the sql query to:

$sql = 
"SELECT up.*
FROM user_provider up
WHERE up.provider_id = $provider_id AND (up.user_provider_id = $user_provider_id OR up.parent_id = $user_provider_id) ";

But this will not return children of children and lower.

 

So, I suspect that I need to retain my original sql query and do the tree processing in php, but limit the parent to $user_provider_id and all children of that parent.

 

Not quite sure on the best way to approach this - any help greatly appreciated.