Using PHP with a recursive MySQL query

I’m trying to build a membership ‘downline’ list with PHP and MySQL. The site isn’t MLM per se but we have a need to see the full downline for a particular member. This could be 10 …20 or even 50 levels deep.

Currently I have 10 nested queries that take the first 10 levels and place them into a temporary table. Here’s an example using 3 levels:


		// Recursively query downline member
		$nextres=@mysql_query("SELECT id,refid,firstname,lastname,email FROM members WHERE refid=$id");
		while($resinfo=@mysql_fetch_object($nextres))
		{
			$userid=$resinfo->id;
			$refid=$resinfo->refid;
			$email=$resinfo->email;
			$nextname=$resinfo->firstname." ".$resinfo->lastname;

			// Get level 2
			$l2res=@mysql_query("SELECT COUNT(*) AS cnt FROM members WHERE refid=$userid");
			$l2cnt=@mysql_result($l2res,0);
			if($l2cnt > 0)
			{
				$l2nextres=@mysql_query("SELECT id,refid,firstname,lastname,email FROM members WHERE refid=$userid");
				while($l2resinfo=@mysql_fetch_object($l2nextres))
				{
					$l2userid=$l2resinfo->id;
					$l2refid=$l2resinfo->refid;
					$l2email=$l2resinfo->email;
					$l2nextname=$l2resinfo->firstname." ".$l2resinfo->lastname;

					// Get level 3
					$l3res=@mysql_query("SELECT COUNT(*) AS cnt FROM members WHERE refid=$l2userid");
					$l3cnt=@mysql_result($l3res,0);
					if($l3cnt > 0)
					{
						$l3nextres=@mysql_query("SELECT id,refid,firstname,lastname,email FROM members WHERE refid=$l2userid");
						while($l3resinfo=@mysql_fetch_object($l3nextres))
						{
							$l3userid=$l3resinfo->id;
							$l3refid=$l3resinfo->refid;
							$l3email=$l3resinfo->email;
							$l3nextname=$l3resinfo->firstname." ".$l3resinfo->lastname;


							@mysql_query("INSERT INTO tmpdownline(userid,refid,name,email,level) VALUES($l3userid,$l3refid,'$l3nextname','$l3email',3)");
						}
					}

					@mysql_query("INSERT INTO tmpdownline(userid,refid,name,email,level) VALUES($l2userid,$l2refid,'$l2nextname','$l2email',2)");
				}
			}

			@mysql_query("INSERT INTO tmpdownline(userid,refid,name,email,level) VALUES($userid,$refid,'$nextname','$email',1)");
		}

Clever, compact code isn’t my forte’ :blush: so I’m hoping some clever person can show me a much more elegant way to do this that will keep digging to the next level until it has extracted all ‘downline’ records.

Not sure exactly what a membership downline is, but I think what you want is just a loop. It’ll keep running the queries until it hits one where it should stop. $arr_members holds the members, the first one being the top most level.



$ref_id = 42;
$arr_members = array();

while( true )
{
  $rs = mysql_query( 'SELECT whatever FROM table WHERE refid = $refid' );
  if( !mysql_num_rows( $rs ) )
    break;

  $row = mysql_fetch_array( $rs );
  $arr_members[] = $row;
  $refid = $row['userid']
}

Btw, if you’re just storing a tree (hierarchical data) then I’d strongly recommend reading the following site point article. It’d be much more efficient to use left and right indexes versus a child/parent relationship. You could retrieve all members under another member in one database query.

I should have been a bit more verbose on the structure of the member records. It’s like a pyramid structure and the only relationship in the table is between the member and their referrer. So, lets say we had 10 members. Here’s how the referrals might look.


Userid    ReferrerID
1                -
2                1
3                1
4                2
5                3
6                2
7                6
8                7
9                8
10              9

So … member 1 has no referrer, members 2 and 3 were referred by member 1, member 4 was referred by member 2 etc etc…

There’s no direct relationship between member 1 and member 10 but there IS a relationship there. If we look at the downline (like a 'genealogy) we get a path from 10 to 1 that goes 10->9->8->7->6->2->1. Tracing it backwards like that is fairly easy. There’s always a single line of IDs. On the other hand, if you start at member number 1 and try to find all of his referrals and all of his referrals referrals and so on, it gets a bit trickier.

So, to get that type of ‘genealogy’ it needs a query that can recurse to any number of levels until the last referral in that ‘line’ is found and then go back to level 2 and follow the next referral line. Member number 1 may have referred 100 people who all referred people who all referred people etc to varying depths (i.e. one ‘line’ may go 3 levels deep while another may go 30 levels deep).

I’ve quickly modified the original code so as to be recursive.

Please pay attention to the article that UFTimmy mentioned about hierarchical data as it’s a good one and will make things a lot easier for you in the future.

Disclaimer: The following has been worked from the provided code. It has not been tested. Do not use if allergic to peanuts. Lifejackets are to be found under your seat. Here be dragons.

function recurseMember($userid, $level=1) {
  $result=@mysql_query("SELECT id,refid,firstname,lastname,email FROM members WHERE refid=$userid");
  while($info=@mysql_fetch_object($result))
  {
    $userid=$info->id;
    $refid=$info->refid;
    $email=$info->email;
    $nextname=$info->firstname." ".$info->lastname;
    @mysql_query("INSERT INTO tmpdownline(userid,refid,name,email,level) VALUES($userid,$refid,'$nextname','$email',$level)");
  }
  // Get user
  $result=@mysql_query("SELECT COUNT(*) AS cnt FROM members WHERE refid=$userid");
  $count=@mysql_result($result,0);
  if($count > 0) {
    recurseMember($userid, $level + 1);
  }
}

// Recursively query downline member
processMember($id);

To just beat it into the ground one more time… If you want to do this the right way, read the article. Use lft and rgt indexes instead of a member -> referrer relationship. It will be so much more efficient. Given a member id, you could retrieve everyone who is under that person in the “pyramid” (called a tree in computer science) in one database query.

1 Like

Thanks guys. That’s perfect. After reading the article you both suggested and massaging the code (just a little) I was able to achieve exactly what I was after. It was a bit of a shock actually … some downlines in the member table went to a depth of 70 levels.

In this case the database already existed so I couldn’t use the right/left approach but I will definitely refer back to that article the next time I want to create a hierarchical structure within a table.

Thanks again :slight_smile: