Creating A Dynamic Downline


#1

Hello All,

I’m building a project that involves a 7 tiered affiliate program and I’m trying to wrap my head around displaying this downline of affiliates to the members. I’m using object oriented code with a common functions include page.

No problems in creating a members “frontline”, and showing the entire downline in a tree view seems to be a simple re-iteration of this same code. It’s the recursion factor that’s killing me.

If someone could give me some help on this I would certainly appreciate it.

Here’s what I have to create a members “frontline” affiliates:

[code] //--------------------------------------------------------------------------
function ocd_list ()
{
$this->mainTemplate = “./templates/downline.tpl”;
$this->pageTitle = “My Downline”;
$this->pageHeader = “My Downline”;
$siteURL = $this->db->GetOne (“Select value From settings Where keyname=‘SiteUrl’”);
$total = $this->db->GetOne (“Select Count(*) From members Where referrer_id=’{$this->member_id}’ And member_id!=’{$this->member_id}’”);
$username = $this->db->GetOne (“Select username From members Where member_id=’{$this->member_id}’”);

    $this->data = array (
        "MAIN_HEADER" => $this->pageHeader,
        "HEAD_LEVEL" => "<b>Level</b>",
        "HEAD_USERNAME" => "<b>ID</b>",
		"HEAD_NAME" => "<b>Name</b>",
		"HEAD_MEM_TYPE" => "<b>Title</b>",
		"HEAD_PV" => "<b>PV</b>",
		"HEAD_GV" => "<b>GV</b>",
        "HEAD_JOINED" => "<b>Join Date</b>",
        "MAIN_REFLINK" => $siteURL . "?ref=" . $this->member_id,
		"MAIN_PROFLINK" => $siteURL . "$username",
    );

    $bgcolor = "";
    if ($total > 0)
    {
        $result = $this->db->ExecuteSql ("Select * From members Where referrer_id='{$this->member_id}' Order By {$this->orderBy} {$this->orderDir}");  		
		for ($i = 0; $i < $total; $i++)
        {
            $row = $this->db->FetchInArray ($result);
			$id = $row['member_id'];
			$lev = $i+1;
			$mem_id = $row['member_id'];
			$username = $row['username'];
			$name = $row['first_name'] . " " . $row['last_name'];
			$level = $row['level_id'];
			$type = $this->db->GetOne ("Select `title` From `levels` Where level_id='$level'");
			$pv = "";
			$gv = "";
			$reg_date = date ("F d, Y", $row['reg_date']);           
		
		$bgcolor = ($bgcolor == "") ? "#E7E7E7" : "";
					
					$this->data ['TABLE_ROW'][] = array (
					"ROW_LEVEL" => "$lev",
					"ROW_USERNAME" => $username,
					"ROW_NAME" => $name,
					"ROW_MEM_TYPE" => $type,
					"ROW_PV" => $pv,
					"ROW_GV" => $gv,
					"ROW_JOINED" => $reg_date,
					"ROW_BGCOLOR" => $bgcolor,
					);	
		}
        $this->db->FreeSqlResult ($result);		
	} 
    else
    {
        $bgcolor = ($bgcolor == "") ? "#E7E7E7" : "";
        $this->data ['TABLE_EMPTY'][] = array (
            "ROW_BGCOLOR" => $bgcolor
        );
    }
}   

}[/code]

I know this needs a recursive foreach loop, or perhaps a couple of them, but not sure where to put them, or how to integrate them into the functions.

Thanks a bunch.

Josh


#2

What exactly do you want it to do? And what have you tried? I’m not exactly sure what it’s supposed to do and what it does instead.

Also, be very careful with recursive fuctions (there’s no such thing as a recursive loop). On PHP they’re not fatal for your server resources, as there’s a builtin timeout, but having to debug recursive functions is especially tricky.


#3

The function should show a tree view of a members downline with details on that individual.

Something like this:

Level   ID        Name           PV              GV           Joined
1      jjones     Joe Jones     300.00      5000.00      April 14, 2007
 2     sjones    Steve Jones  100.00      4000.00      April 18, 2007
  3    tjones     Tom Jones    300.00      3000.00      April 19, 2007
  3    rjones    Rick Jones     100.00      100.00        April 20, 2007
1      jmoore   John Moore    400.00     5000.00      April 15, 2007

Basically these are the same call over and over finding each individuals frontline and then placing them in a specific order.

jjones sponsored by the logged in member
sjones sponsored by jjones
tjones, rjones sponsored by sjones
jmoore sponsored by the logged in member

The function needs to loop down calling each individuals “referrer_id” from the db till it finds no more member sponsored then move to the next “frontline” affiliate and do it again, then display it all in a tree similar to above.

I’ve tried using all 3 types of loops that I’m familiar with for, foreach, and while and I can’t seem to get anything but a single result set.

I mentioned recursive (loops my bad sorry) functions as I found a post on phpbuilder from several years ago that was basically the same thing, but I tried implementing it and it wouldn’t work at all.

Appreciate the help, thanks.

Josh


#4

Ah I see now. So the link would be referer_id.

[php]
function getReferer($myID, $output = “”) {
$result = mysql_query("SELECT * FROM referers WHERE reference_id = ".$myID);
while ($reference = mysql_fetch_array($result)) {
$output = $someTemplateWithReferenceData;
getReferer($reference[‘sponsorID’], $output);
}
return $output;
}
[/php]

That way, you get a recursive function that keeps score in the variable $output. It’d be best to put the presentation layer in there (that means the HTML to display the tree).


#5

Ok I’m a little confused still since you used different fields and tables in the example you gave so let me re-write this and see if I have it right for my app.

[php]
function getReferer($myID, $output = “”) {
$result = $this->db->ExecuteSql ("SELECT * FROM members WHERE referrer_id = ".$myID);
while ($reference = $this->db->FetchInArray ($result)) {
$output = $someTemplateWithReferenceData;
getReferer($reference[‘referrer_id’], $output);
}
return $output;
}[/php]

This being compared to the actual code above.

I’m also confused as to $someTemplateWithReferenceData my php code is produced through an array to the corresponding .tpl template file.

Do I place that array into a variable and call that through the section $someTemplateWithReferenceData in your code?

Sorry to be difficult. What you’ve written seems right just want to be sure I implement it correctly.

Josh


#6

I think it’s best to replace $someTemplateWithReferenceData with your template, while keeping in mind that you have to substitute/parse all variables with the correct values.


#7

Just to close this out and leave an answer for anyone else that may be searching, here’s the final working code:
[php]
function GetNextDownline ($member_id, $level, $bgcolor = “”)
{
$level_mark = “”;
for ($i = 1; $i < $level; $i++) $level_mark .= " ";
$level_mark .= $level;

    $data = array ();
    $result = $this->db->ExecuteSql ("Select * From members Where placement_id='$member_id' Order By {$this->orderDefault} {$this->orderDir}");
    while ($row = $this->db->FetchInArray ($result))
    {
        $mem_id = $row['member_id'];
        $username = $row['username'];
        $name = $row['first_name'] . " " . $row['last_name'];
        $level_id = $row['level_id'];
        $referrer = $row['referrer_id'];
        $type = $this->db->GetOne ("Select `title` From `levels` Where level_id='$level_id'");
        $pv = $this->GetPV ($mem_id);
        $gv = $this->GetGV ($mem_id);
        $reg_date = date ("F d, Y", $row['reg_date']);            
        $class = ($referrer == $this->member_id) ? "error" : "";
                
        $bgcolor = ($bgcolor == "") ? "#E7E7E7" : "";
        $data [] = array (                          
            "ROW_LEVEL" => "<span class='$class'>$level_mark</span>",
            "ROW_USERNAME" => "<span class='$class'>$username</span>",                
            "ROW_NAME" => "<span class='$class'>$name</span>",
            "ROW_MEM_TYPE" => "<span class='$class'>$type</span>",
            "ROW_PV" => "<span class='$class'>$pv</span>",
            "ROW_GV" => "<span class='$class'>$gv</span>",
            "ROW_JOINED" => "<span class='$class'>$reg_date</span>",
            "ROW_BGCOLOR" => $bgcolor,
        );

        $referrals = $this->db->GetOne ("Select Count(*) From `members` Where placement_id='$mem_id' And member_id!='$mem_id'", 0);
        
        if ($referrals > 0) {
            $array = $this->GetNextDownline ($mem_id, ($level + 1), $bgcolor);
            array_push ($data, $array);
        }
    }
    $this->db->FreeSqlResult ($result);

    return $data;
}[/php]

Obvisouly you’ll need to change it for your use.

J