• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 57
  • Last Modified:

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
0
Mlungisi Ndlela
Asked:
Mlungisi Ndlela
  • 7
  • 5
3 Solutions
 
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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