Organisation chart [php/mysql]

[topgun]

Hi phpfreaks,

 

I am looking for some support and help on makings a organisation chart, i have a sql database which i feel should work. (Attached)

 

I am fairly weak with php and mysql so my skills are very limited as i am new to the whole language.

 

I am looking to make a hierarchical structure to display the staff structure with the owner at the top moving further down until the person is not superior to anyone.

 

|Owner|

      |

|Co Owner|

 

Any help or direction would be great. I have tried may way but not reaching my desired outcome.

 

Thank you

 

 

 

[attachment deleted by admin]

[Brian W]

like

|Owner|

      |

|Co Owner|

or would it be like

              |Owner|

              |    |

|Co Owner| |Co Owner|

                              |                     

                            |Joe|

One can have only single branch and is easy, the other has multiple and is VERY difficult to achieve from my understanding.

 

[topgun]

Sorry for not being clear.

 

Its for multiple levels

[Brian W]

Google offers a very powerful tool that I've never had any luck with.

http://code.google.com/apis/visualization/documentation/gallery/orgchart.html

 

Best of luck, sorry I can't be anymore help

[T-Bird]

I just created a similar code.  Mine was a directory tree for some server files, but is the same organizational system (one directory has x number under it which have x number under them etc.)

 

I made each "node" an array.  It was structured something like.

 

node = array(name,info,sub)

 

So say you have an OWNER node.  You would set name as owner, info as whatever you need to know about him/her and sub would hold another array (numeric not associative).  Each index of this sub array holds another node.

 

Say you have this basic tree

 

                     --[EMPLOYEE1]
         -[MANAGER1]-|-[EMPLOYEE2]
[OWNER]-|                                --[EMPLOYEE3]
         -[MANAGER1]-[ASSISTANT MANAGER]-|-[EMPLOYEE4]
                                         --[EMPLOYEE5]

 

As an array this would be

 

$employees = array(
0 => array(
	'name' => 'OWNER',
	'info' => 'I RULE',
	'sub' => array(
		0 => array(
		'name' => 'MANAGER 1',
		'info' => 'I CALL THE SHOTS',
		'sub' => array(
			0 => array(
				'name' => 'EMPLOYEE1',
				'info' => 'SLAVE LABORER',
				'sub' => NULL
				),
			1 => array(
				'name' => 'EMPLOYEE2',
				'info' => 'SLAVE LABORER',
				'sub' => NULL
				)
			),
		1 => array(
		'name' => 'MANAGER 2',
		'info' => 'I CALL THE SHOTS',
		'sub' => array(
			0 => array(
				'name' => 'ASSISTANT MANAGER',
				'info' => 'MORE WORK LESS PAY',
				'sub' => array(
					0 => array(
						'name' => 'EMPLOYEE3',
						'info' => 'SLAVE LABORER',
						'sub' => NULL
						),
					1 => array(
						'name' => 'EMPLOYEE4',
						'info' => 'SLAVE LABORER',
						'sub' => NULL
						),
					2 => array(
						'name' => 'EMPLOYEE5',
						'info' => 'SLAVE LABORER',
						'sub' => NULL
						)
					)
				)
			)
		)
	)
);

 

To find, for example, the name of employee5 you would use

 

$name = $employees[0]['sub'][1]['sub'][0]['sub'][2]['name'];
//first go to 0th element in the array, the owner node
//then go to owner node's sub array
//go to 1th element in owner's sub array, the manager2 node
//go to manager2's sub array
//go to 0th element in owner's sub array, the assistant manager node
//go to assistant manager's sub array
//go to the 2th node in assistant manager's sub array, the employee5 node
//get the 'name' value from employee5 node.

 

Now that you have a basic data structure for your employee table it's just a matter of populating the list and formatting output.

 

Hope that helps.

[topgun]

Brian W: Thank you for you time.

 

T-Bird:

 

Thank you for your help and support,

 

Could i replace "name" "info" in each array to pull from a mysql query.

 

[premiso]

I must be in a nice mood:

 

<?php
// Mysqlconnection needs to be started before calling the functions.  Usage is at the end of the file.

function retrieveOrgList($parentid=false) {
$parentid = ($parentid==false)?0:$parentid;
$query = mysql_query("SELECT org.id, org.company_name, rel.superior, userid, us.firstname, us.surname, us.job_title, us.job_role, us.image FROM organisations org, relationships rel, users us WHERE rel.organisationid = org.id AND rel.userid = us.id AND superior = " . $parentid) or die(mysql_error());
$numRows = mysql_num_rows($query);

$orgArray = "none";
if ($numRows > 0) {
	$orgArray=array();
	while ($row = mysql_fetch_assoc($query)) {
		$orgArray[$row['company_name']]['id'] = $row['id'];
		$orgArray[$row['company_name']]['superior'] = $row['superior'];
		$orgArray[$row['company_name']]['userid'] = $row['userid'];
		$orgArray[$row['company_name']]['firstname'] = $row['firstname'];
		$orgArray[$row['company_name']]['surname'] = $row['surname'];
		$orgArray[$row['company_name']]['job_title'] = $row['job_title'];
		$orgArray[$row['company_name']]['job_role'] = $row['job_role'];
		$orgArray[$row['company_name']]['image'] = $row['image'];
		$orgArray[$row['company_name']]['unders'] = retrieveCategoryList($row['userid']);
	}
}

return $orgArray;
}

function displayOrgs($org=false, $charDisp=0) {
if (!$org) {
	if ($charDisp != 0) {
		$orgs = retrieveCategoryList($charDisp);
	}else {
		$orgs = retrieveCategoryList();
	}
	foreach ($orgs as $org) {
		displayOrgs($org);
	}
}else {
	echo str_repeat("_", $charDisp) . $org['firstname'] . "<br />";
	if ($org['unders'] != "none") {
		foreach ($org['unders'] as $value) {
			displayOrgs($value, $charDisp+1);
		}
	}
}
}

displayOrgs(false, 0);

echo "<pre>";
// Lets just pull out a set of organizations.
print_r(retrieveOrgList());
?>

 

And a side note:

insert into `users` (`id`, `firstname`, `surname`, `job_title`, `job_role`, `image`) VALUES (4, 'jerk', '', '', '', '');

I had to add that to mysql, since you have a relationship to a user4, but no user 4.

[topgun]

wow, thank you so much i wasn't expecting that.

 

I am just about to get some sleep and noticed i got a notification, so many thanks and i will try that out when i wake up.

 

PS. is there no thank you button on this forum?

[premiso]

PS. is there no thank you button on this forum?

 

No the forum admins do not believe in karma, if you really want to thank me, donate some money to this site, nothing major like $1-$5 is helpful.  (If you can).

 

With that code above, you will have to modify Display Orgs to change the output to be how you want it, this was originally a category script I created so yea, it will go through as many employees/supervisors as there are in the DB since it is recursive. Should work great.

[topgun]

Fatal error: Call to undefined function retrieveCategoryList() in C:\wamp\www\test.php on line 45

 

Just got the following error.

[DarkSuperHero]

i think you would replace....

	if ($charDisp != 0) {
		$orgs = retrieveCategoryList($charDisp);
	}else {
		$orgs = retrieveCategoryList();
	}

 

 

with

	if ($charDisp != 0) {
		$orgs = retrieveOrgList($charDisp);
	}else {
		$orgs = retrieveOrgList();
	}

 

this might be it from what i can tell....not sure though didnt look into it too much...try it out! :-)

 

[topgun]

Thanks, yeah that was the problem.

[premiso]

Fatal error: Call to undefined function retrieveCategoryList() in C:\wamp\www\test.php on line 45

 

Just got the following error.

 

Yea, I forgot to change all instances of it, sorry. Like I said it used to be a category script =) Thanks DarkSuperHero for correcting it.

[topgun]

Thanks to everyone who helped especially premiso and t-bird.

 

For the styling this is a CSS thing isn't it?

 

PS. Sorry about all the questions.

[premiso]

No, you have to change this line:

 

echo str_repeat("_", $charDisp) . $org['firstname'] . "<br />";

 

To echo out the code you want it to display as. The str_repeat is for the indentation, you can change that value if you want to, then you can surround that in whatever html/css code you want to. You will probably have to play around with it doing trial and error until you get it how you want.

[topgun]

I was thinking that glad i am understanding this code.

 

Thanks again

[topgun]

It seems that the script doesn't check if there is more than one ID to display.

 

example

 

                        |owner|

      |employer1|              |employer 2|

                                |employer 3| |employer 4|

 

The script will look at the owner and see that employer 2 is linked to employer 4 and will not display the full structure. So that employer 1 and employer 2 won't be displayed.

[topgun]

anyone any ideas?

[T-Bird]

If you can tell me what part of the code contains the logic error then I can tell you how to fix it.

 

I'm not reading through and breaking down the entire program to learn how it works and why it's broken, and then still try to fix it.