Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

MySQL query help

I have a MySQL db with the tables listed below.  I need to display hierarchical reporting (i.e., the logged in user should see all of his/her direct-reports, all of his/her direct-reports' direct-reports and so on about 6 levels deep.  The attached function works for the two reporting level only -- VP sees her direct reports (directors), along with each of her director's direct reports (managers).

What I need now is to add some additional levels so the VP will also see each of the director's manager's direct reports (associates) and each of the director's manager's associate's direct reports (assistants).

Thanks!

TABLES
TBL_USERS
id
last_name
first_name
username
email
password
employee_id
role
is_manager
reports_to
loa
rto_name
rto_fname

TBL_DEPARTMENTS
id
dept_name

TBL_COMPLIANCE_REPORTS
id
program_id
user_id
compliance_status
date
approve
comments
reason
function multiple_hierarchy_ids($uid)
{
	$repIdsArrMgrs=array();
	
	// to get the list of all managers M who directly reports to this logged in admin
	$queryMgrRepToAdmin = "SELECT id FROM tbl_users WHERE loa!='1' AND status='1' AND (reports_to LIKE '".$uid."' OR reports_to LIKE '".$uid.",%' 
		OR reports_to LIKE '%,".$uid.",%' OR reports_to LIKE '%,".$uid."')";
	
	$resultMgrRepToAdmin	=	mysql_query($queryMgrRepToAdmin) or die(mysql_error());
	while($rowMgrRepToAdmin		=	mysql_fetch_assoc($resultMgrRepToAdmin))
	{
	
		array_push($repIdsArrMgrs, $rowMgrRepToAdmin['id']);
	
	} //end while($rowMgrRepToAdmin		=	mysql_fetch_assoc($resultMgrRepToAdmin))
	
	
	if(count($repIdsArrMgrs) > 0)
	{
		$repIdsArrAssoc=array();
		
		for($cntrR=0;$cntrR<count($repIdsArrMgrs);$cntrR++)
		{
			$queryAssocRepToMgrs = "SELECT id FROM tbl_users WHERE loa!='1' AND status='1' AND (reports_to LIKE '".$repIdsArrMgrs[$cntrR]."' OR reports_to LIKE '".$repIdsArrMgrs[$cntrR].",%' 
				OR reports_to LIKE '%,".$repIdsArrMgrs[$cntrR].",%' OR reports_to LIKE '%,".$repIdsArrMgrs[$cntrR]."')";

			$resultAssocRepToMgrs	=	mysql_query($queryAssocRepToMgrs) or die(mysql_error());
			
			while($rowAssocRepToMgrs		=	mysql_fetch_assoc($resultAssocRepToMgrs))
			{
				array_push($repIdsArrAssoc, $rowAssocRepToMgrs['id']);
			}
			
		} //end for($cntrR=0;$cntrR<count($repIdsArrMgrs);$cntrR++)
		
	} //end if(count($repIdsArr) > 0)
	
	if(count($repIdsArrAssoc))
		$assocIdsRepArr = array_unique(array_merge($repIdsArrMgrs, $repIdsArrAssoc));
	else		
	if(count($repIdsArrMgrs))
		$assocIdsRepArr = $repIdsArrMgrs;
	else
		$assocIdsRepArr = array(0 => '-1');
		
	return $assocIdsRepArr;
	
}


function multiple_hierarchy_ids_loa_status($uid)
{
	$repIdsArrMgrs=array();
	
	// to get the list of all managers M who directly reports to this logged in admin
	$queryMgrRepToAdmin = "SELECT id FROM tbl_users WHERE loa!='1' AND status='1' AND (reports_to LIKE '".$uid."' OR reports_to LIKE '".$uid.",%' 
		OR reports_to LIKE '%,".$uid.",%' OR reports_to LIKE '%,".$uid."')";
	
	$resultMgrRepToAdmin	=	mysql_query($queryMgrRepToAdmin) or die(mysql_error());
	while($rowMgrRepToAdmin		=	mysql_fetch_assoc($resultMgrRepToAdmin))
	{
	
		array_push($repIdsArrMgrs, $rowMgrRepToAdmin['id']);
	
	} //end while($rowMgrRepToAdmin		=	mysql_fetch_assoc($resultMgrRepToAdmin))
	
	
	if(count($repIdsArrMgrs) > 0)
	{
		$repIdsArrAssoc=array();
		
		for($cntrR=0;$cntrR<count($repIdsArrMgrs);$cntrR++)
		{
			$queryAssocRepToMgrs = "SELECT id FROM tbl_users WHERE loa!='1' AND status='1' AND (reports_to LIKE '".$repIdsArrMgrs[$cntrR]."' OR reports_to LIKE '".$repIdsArrMgrs[$cntrR].",%' 
				OR reports_to LIKE '%,".$repIdsArrMgrs[$cntrR].",%' OR reports_to LIKE '%,".$repIdsArrMgrs[$cntrR]."')";

			$resultAssocRepToMgrs	=	mysql_query($queryAssocRepToMgrs) or die(mysql_error());
			
			while($rowAssocRepToMgrs		=	mysql_fetch_assoc($resultAssocRepToMgrs))
			{
				array_push($repIdsArrAssoc, $rowAssocRepToMgrs['id']);
			}
			
		} //end for($cntrR=0;$cntrR<count($repIdsArrMgrs);$cntrR++)
		
	} //end if(count($repIdsArr) > 0)
	
	if(count($repIdsArrAssoc))
		$assocIdsRepArr = array_unique(array_merge($repIdsArrMgrs, $repIdsArrAssoc));
	else		
	if(count($repIdsArrMgrs))
		$assocIdsRepArr = $repIdsArrMgrs;
	else
		$assocIdsRepArr = array(0 => '-1');
		
	return $assocIdsRepArr;
	
}

Open in new window

0
kimberlys777
Asked:
kimberlys777
1 Solution
 
Ray PaseurCommented:
This sounds more like a need for application development, rather than a question we can answer.  If it were my app, I would probably start looking at the relationships between the fields named employee_id, role, is_manager, reports_to.  Maybe something in there will enable you to create a hierarchy.
0
 
kimberlys777Author Commented:
ok, thanks.
0
 
kimberlys777Author Commented:
The expert was unable to answer the question but did give me a place to start looking for an answer
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Ray PaseurCommented:
Wow, "thanks" for the worst grade you can possibly give anyone at EE.  Going forward you might want to engage in some dialog with the Experts who post comments.
0
 
David Johnson, CD, MVPOwnerCommented:
It may also be easier to exclude reports than include them..
0
 
virmaiorCommented:
Looking at the code, it's quite poorly written and probably executes very slowly.

If I were writing, I would reorganize the procedure into two phases:

(1) accumulate all of the employee_ids that a particular user has view rights for
(2) query for that list based on that id.

As of right now, it looks it searches a comma delimited text list???

to accomplish one, you just need a left join system where you search by the first id for all of their reports and then their reports reports.  Take those ids and recurse.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now