It's like a tree pyramid. I want to get the members sponsored by the current user. The result will be used to get its sponsored members and so on until everyone is read. How can I achieve this? I currently have this code but I can only go to level 8 since its a lot of work. I thought of recursion but I have no idea how to do it. I tried to study it but it's quite hard. can anyone please help me on this? thanks

$parent = $_GET['cued'];
$result = mysql_query("SELECT id FROM users WHERE agent = '$parent'");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// then right here, I use $sponsored to query the members sponsored by the result above..

Could you please add comments into your code so I can see how your code relates to your context. As it is your code looks totally different to what you are describing so please add comments into the code to make it more clear then I may be able to help.

// This is the Number 1 Member at the top of the Pyramid

$id = $_GET['cued'];

// Right here, I want to get the members who were sponsored by the Number 1 using his ID

$result = mysql_query("SELECT id FROM users WHERE sponsor = '$id'");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// The $sponsored will look like this "sponsor = '2' or sponsor = '3' or sponsor = '4' and so on...
// I will now use $sponsored in my WHERE statement

$result = mysql_query("SELECT id FROM users WHERE ($sponsored)");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// Then the result of the 2nd Query will be used to the 3rd query  and so on...

Basically, I am using the result of each query to do another query until all the sponsored members are exhausted. ( It is by level )

Thanks a lot!

Try the following

<?php
// This is the Number 1 Member at the top of the Pyramid
 
$id = mysql_real_escape_string($_GET['cued']);
 
// Right here, I want to get the members who were sponsored by the Number 1 using his ID
 
$r = mysql_query("SELECT `id` FROM `users` WHERE `sponsor` = '$id'");
  	$output = array();
 	while ($row = mysql_fetch_array($r)) {
    $output[] = 'sponsor = "'.mysql_real_escape_string($row['id']).'" ';
	}
	$sponsored = implode(' or ', $output);
$re=mysql_query('SELECT * FROM `users`');
$count=mysql_num_rows($re);
// The $sponsored will look like this "sponsor = '2' or sponsor = '3' or sponsor = '4' and so on...
// I will now use $sponsored in my WHERE statement

while(mysql_num_rows($r)<$count){
$r = mysql_query("SELECT `id` FROM users WHERE ($sponsored)");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = '`sponsor` = "'.mysql_real_escape_string($row['id']).'" ';
	}
	$sponsored = implode(' or ', $output);
 }
// Above loops through the 2nd, 3rd, 4th etc Query until all rows in the table are matched.

It seems that the query took so long and recurred so many times.

It seems that the query took so long and recurred so many times.

Not much you can do about that especially if its a large table.
But you could always try removing line 21 of my code to increase the speed ( $output=array() ).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.