[RESOLVED] Displaying Parent and child data

[happyme]

I have a database of employees.

The row for each employee gives the name of their line manager and department. Each employee has one line manager (parent) and each line manager has several staff (children) some of whom also manage other staff, who may in turn also manage staff.

The company is expanding so staff who currently don't manage other staff may do so in future without changing their place in the heirarchy.

My question is, how can I display all of this data clearly on one page?

Ideally laid out so each level of heirarchy is indented from the previous one.

N.B. I need to do this without multiple calls to the SQL server as it can be rather slow.

TIA

H

[rulian]

Can you provide us the DB structure of how the information is organized.

Basically, I would be JOIN ing the table to itself, the trick is the level of parent/child will be determined by the number of joins.

The only problem is you have an undetermined amount of joins.
So I'm curious as to how this plays out

[happyme]

My database is like this (filled with made up names):

0 means no line manager

id Surname Firstname Linemanager
1 Bloggs Joe 4
2 Dibble Dave 5
3 Frank Fred 0
4 Gargle Gavin 3
5 Hall Harry 3

Which needs to display like this:

Fred Frank
- Gavin Gargle
- Joe Bloggs
Harry Hall
- Dave Dibble

I'm thinking of seeing if I can do something with a function which calls itself (it that possible? No doubt I'll find out soon) to get round the fact I don't know how many levels there will be.

I'm ordering my data by Line Manager and Surname when I get it from the server.

I'll keep you posted

H

[rulian]

Yeah I've done function to display nested forum, I guess this would have the same functionality

So if you Simply an array of indexs $Person[parent id][Person id]="name"

You can do

function getRows()
{
$SQL="SELECT * from `tablename` ORDER BY `Linemanager`,`id`"); 
if ($SQL) 
{
	while($row=mysql_fetch_assoc($SQL))$retVar[]=$row; 
	return $retVar; 
}
else return false;
}

function findChild($parent, $arr)
{
	while(list($id) = each($arr[$parent]))
	{
	echo $arr[$parent][$id]."<br>"; 
	if (isset($arr[$id])) fundChild($id, $arr); 
	}
}


$People=getRows(); 

/// list them
findChild(0,$People); 
//// just becare if there are no 0 you gotta build an exception, or use key($arr) to list the first topmost level of the array

the rest is in the formatting

[happyme]

Thank you

In the meantime I wrote my own solution:

function showchildren($DataArray,$currentid,$arraysize)
	{
	$numberofkids = 0;

for ($i = 0; $i <= $arraysize-1; $i++) 
	{
	if ($DataArray[$i]["LineManager"] == $currentid)
		{
		$ChildrenArray[] = $i;
		$numberofkids ++;
		}

	}
	echo "<blockquote>";			

	if($numberofkids)
		{
		foreach ($ChildrenArray as $value) 
			{
			//get the id of the child			
			$ChildsId = $DataArray[$value]["id"];

			//Display children

			printf("<p>%s %s</p>",$DataArray[$value]["Firstname"], $DataArray[$value]["Surname"]);

			// See if they have any children
			showchildren($DataArray,$ChildsId,$arraysize);

			}
		}
	echo "</blockquote>";	
}

It works and I'm pleased with it!

Thanks for your input

H