referral system using modified preordered tree traversal
Posted: Tue Apr 08, 2008 9:36 pm
Currently, this is how I'm displaying the number of people in a given persons downline (their referrals, their referrals referrals, etc..)
I'm wondering if there's a way I can query for all the members within a specific level limit (say 5 levels deep) without using the $downcount variable inside of a condition. If I can, this will allow me to paginate the results.
Err.. hope I can make myself clear enough. I want to be able to get all members of bob's downline 5 levels deep within a query. Hopefully someone has good experience with referral systems and downlines.
Code: Select all
function display($root, $level_limit)
{
//count
$downcount = 0;
// retrieve the left and right value of the $root node
$result = mysql_query('SELECT `left_boundary`, `right_boundary` FROM `downline` WHERE `username` = "'.$root.'";') or die(mysql_error());
$row = mysql_fetch_array($result);
// start with an empty $right stack
$right = array();
// now, retrieve all descendants of the $root node
$result = mysql_query('SELECT `username`, `left_boundary`, `right_boundary` FROM `downline` WHERE `left_boundary` BETWEEN '.$row['left_boundary'].' AND '.$row['right_boundary'].' AND `exists` = 1 GROUP BY `username` ORDER BY `left_boundary` ASC;') or die(mysql_error());
// display each row
while($row = mysql_fetch_assoc($result))
{
// only check stack if there is one
if(count($right)>0)
{
// check if we should remove a node from the stack
while($right[count($right)-1] < $row['right_boundary'])
{
array_pop($right);
}
}
// display indented node title
if($row['username'] != $_SESSION['user_name'])
{
$level = 1*count($right);
if($level <= $level_limit)
{
$downcount++;
}
}
// add this node to the stack
$right[] = $row['right_boundary'];
}
}Err.. hope I can make myself clear enough. I want to be able to get all members of bob's downline 5 levels deep within a query. Hopefully someone has good experience with referral systems and downlines.