Search and for earch result use the for each loop in php and mysql

Hi EE.

I have a database which has some tables and on one of the table call vvx and I have two fields named CoreUser and another one is named refer now what I do is that when a user logs in the system checks on the refer to see if this person has introduced any user to the system and if s/he does that firstly count how many users s/he had introduced then after that I want to check for each user s/he had introduced how many people each introduced, and also for those as well check how many they had introduced.

The table is like this

---------------------------------------|-------------------------------------------------
CoreUser                                | refer
----------------------------------------|--------------------------------------------------
ja                                              |  zzz
--------------------------------------------------------------------------------------------
fgg                                            | ja
---------------------------------------------------------------------------------------------
jhb                                            |zzz
----------------------------------------------------------------------------------------------
trr                                             | fgg
-----------------------------------------------------------------------------------------------

Now you can see that 'zzz' introduced 2 users named 'ja' and 'jhb' and that one of the users introduced by 'zzz' 'ja' had introduced user 'fgg' and that 'fgg' had introduced 'trr' so that what I want to achieve and I know I have to use the foreach loops but I'm stuck in starting this because I want to check it up to level 5
LVL 2
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAsked:
Who is Participating?

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

x
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.

Julian HansenCommented:
What output do you want?

Are you wanting total referred users (down to 5 levels) for a given user
OR
Do you want to produce a report of all users and how many (down to level 5) users they have referred?
0
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
I want to get both because I will also need the total users which all link to 'zzz' which also 'trr' also link because its a chain to 'trr' from 'zzz' so when user 'zzz' log in s/he must also see the total number users within his/her network (tree).

I also need to see how many 'ja' had introduced which is a level 2 of a tree and also see how many 'fgg' had introduced which is level3 and also see how many 'trr' had introduced which is level 4 and so on. Now because there maybe many people 'zzz' had introduced the level counts can be combined for each level have the total number of all users introduced per that level so that I will know that level 2 has this x amount (total) introduced.
0
Julian HansenCommented:
I understand but what I am asking is if you want to query the database per user (for instance to show in a profile)
OR
If you want to pull a report of all users and how many people they have introduced.

What is the use case.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
to show in a profile
0
Julian HansenCommented:
So per user then - you want a function that fetches the number of referred users (down to level 5) given a specific user?
0
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
Yes
0
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
at the moment I'm having something like this that I've just constructed

if($result1st=mysqli_query($db,$sql1st))
{
	// Return the number of rows in result set_error_handler
	$rowcount1st=mysqli_num_rows($result1st); // This is for getting how many 'zzz' had introduced
	while($data =mysqli_fetch_array($result1st,MYSQLI_ASSOC);
	{
		foreach($data as $key => $var) // ***** I'm now confused here *****
		{
			
		}
	}
}

Open in new window

0
Julian HansenCommented:
You can do this with a (long) SQL query.
First we find all the referred users who match our target user
SELECT CoreUser FROM `vvx` WHERE refer = 'zzz'

Open in new window

Now we want to add to that all the people they referred so we take the UNION of the above query and add
SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')

Open in new window

Now we want all the people they added
SELECT CoreUser FROM `vvx` WHERE refer IN (
  SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')
)

Open in new window

Do you see - each time we are just adding another
SELECT CoreUser FROM `vvx` WHERE refer IN (
 ...
)

Open in new window

Around the previous query - you can repeat for as many levels as you want
Example
SELECT CoreUser FROM `vvx` WHERE refer = 'zzz'
UNION
SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')
UNION
SELECT CoreUser FROM `vvx` WHERE refer IN (
  SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')
)
UNION
SELECT CoreUser FROM `vvx` WHERE refer IN (
  SELECT CoreUser FROM `vvx` WHERE refer IN (
    SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')
  )
)
UNION
SELECT CoreUser FROM `vvx` WHERE refer IN (
  SELECT CoreUser FROM `vvx` WHERE refer IN (
    SELECT CoreUser FROM `vvx` WHERE refer IN (
      SELECT CoreUser FROM `vvx` WHERE refer IN (SELECT CoreUser FROM `vvx` WHERE refer = 'zzz')
    )
  )
)

Open in new window

0
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
It seems as if I've figured out this. Here is what I have so far and its working but I'm still building it so that it will go as far as level 5. As of the total tree users I count in combination the $g___ variables which gives me the total of users involved in this tree.

if($result1st=mysqli_query($db,$sql1st))
{
	// Return the number of rows in result set_error_handler
	$rowcount1st=mysqli_num_rows($result1st);
	while($fd=mysqli_fetch_array($result1st,MYSQLI_ASSOC)){
	$hf=$fd['CoreUser'];
	$_SESSION['bvc'] = $hf;
	// Now that We are able to get each user's License that was introduced by this user
	// Now I need to first count how many people each had introduced.
	$sql2nd="SELECT * FROM mydb WHERE	refer = '$hf'";
	if($result2nd=mysqli_query($db,$sql2nd))
	{
		$rowcount2nd=mysqli_num_rows($result2nd);
		// Check to see if result is not 0.
		if($rowcount2nd == 0)
		{
			$g2nd += 0;
		}else{
			$g2nd += $rowcount2nd;
		}
		mysqli_free_result($result2nd);
	}
	else{
		$g2nd = "Err22";
	}
	}
	
	// Checking to see if the count is >0 or not
	if($rowcount1st == 0)
	{
		$g1st = 0;
	}else{
	$g1st = $rowcount1st;
	}
	// Free the result
	mysqli_free_result($result1st);
	// Looping on each result
	
}else{
	$g1st = "Err22";
	$g2nd = "Err22";
	//$_SESSION['rrr'] = $g1st;
}

Open in new window

0

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
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
Thanks but I've already done it like I've just posted above.
0
Julian HansenCommented:
Ok but it is going to be a lot of code doing it your way. There is a recursive code solution but the database version I posted is far more efficient.

You run one query and then iterate over it once.
1
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
I've found the answer and I've already used it which is clear enough
0
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
HTML

From novice to tech pro — start learning today.