Jump to content

Organisation chart [php/mysql]


topgun

Recommended Posts

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]

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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! :-)

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.