How would I incorporate this function into this loop?

I'm creating an org chart. The attached graphic shows you what I've got so far.

I use this function to give my first "tier" which are executives...

		public function executive($vice_president) {
		global $mysqli;
		$sql="select * from org_chart where manager='$vice_president' and last_name<>'$vice_president' order by last_name";
			$err = 'your executive list query didn\'t happen because .'
			.' ERRNO: '
			.' ERROR: '
			.' and the query itself looked like this: '
			trigger_error($err, E_USER_NOTICE);
		while ($row=$query->fetch_array())
			return $result_array;

Open in new window

On the actual page, I call it like this:

$new_chart= new OrgChart; //the class
$executive_list=$new_chart->executive("Khurjekar"); //the function

The funtion returns an array and then I display it with this:

$new_view = new viewOrg; //the view class
$executive_view=$new_view->view_executive($executive_list);// the view_executive function which looks like this:

class viewOrg {
	public function view_executive($executive_list) {
		foreach($executive_list as $executive)
			$briefcase .="<tr><td class=\"avatar\"><span class=\"executive\"><img src=\"images/avatar.jpg\"><a href=\"mailto:";
			$briefcase.="\" style=\"color:#000000; text-decoration:none;\">";
			$briefcase.=stripslashes($executive['first_name']).' '.stripslashes($executive['last_name']);
			$briefcase .=stripslashes($executive['title']);
			$briefcase .="</td><td>";

		return $briefcase;

Open in new window

Now, here's my question: I've got to another loop within my loop. In other words, when I do my "foreach" in my "view_executive" function, I need to list all those people who report to Candace Blaquiere, Frank Carion. So, instead of what I have currently:

Arpana Khurjekar
     Candace Blaquiere
     Frank Carion
     Lynn Carlson

I need...

Arpana Khurjekar
     Candace Balquiere    
          Donald Meador
          Brice Phufal
     Frank Cairon
          person who reports to Frank
          another person who reports to Frank

So, how do I incorporate a second loop using a function?

I could probably do this using a procedural approach, but I wanted to try and do this "right." So, that said, how do I do this?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to implement this recursively, so that indeed when calling the function for the "null" level, it will return all the main Executives, for which you call the same function (recursion) with the "id" of the executive, which will then return all the subordinates of that executive, along with all the subordinates of that one etc .

which means that you need to call the query inside that function, or have a callback technique to perform that query.
for example, I have a (global) DB class that would have such a get_manager_child_records(manager_id) method that returns the listing inside that function. from there, you can use the properties of the records as needed to format the output and still split the data layer from the presentation layer.

pseudo-code of the function (this is not pure php):

function list ( p_manager , db_class)
   $list = db_class->get_manager_child_records(p_manager);
   foreach($list => $manager)
        output += "<li>$manager->name<ul>" + list($manager->id) + "</ul></li>";
  return output;

and you call that once with
echo list(null);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
F PCommented:
Usually within the first foreach loop I set a variable outside of, or just within that gets reset after, which is an integer with a given number of pixels for padding, then use a multiplier to check the children elements and increase them accordingly. Quick, dirty, but it works. Here's an example of what I've done:

	$categories = $obj_ec_category->get_all_categories_array();

	foreach($categories as $category)
		$level_margin      = '0';
		$level_padding_top = '15px';
		$list_style        = 'list-style: none; font-size: 1.4em;';

		if(intval($category['level']) > 1)
			$depth             = (intval($category['level']) - 1) * 40;
			$level_margin      = "{$depth}px;";
			$level_padding_top = '0';
			$list_style        = '';

		echo <<<HTML
	<li itemprop="url" style="margin-left: {$level_margin}; padding-top: {$level_padding_top};{$list_style}">
		<a href="/categories/{$category["category_id"]}/{$category["url_name"]}/" itemprop="name" title="{$category["name"]}" style="text-transform: uppercase;">{$category["name"]}</a>

Open in new window

Bruce GustPHP DeveloperAuthor Commented:
Gentlemen! Thanks for your feedback! I was able to get my original dynamic to work by tweaking things a bit, though I do want to go back at some point and reevaluate it according to the elegance I see in your solutions. Guy, your mention of "recursive" resonates in light of another piece of counsel that I received, so I'll definitely be reviewing that.

Thanks again, gentlemen!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.