Interesting. What you seem to be describing is a "Pyramid Scheme" which implies that any solution you use needs to scale well unless you don't get enough suckers to make the scheme worth while.

Maybe you need to turn your thinking about the database on its head. Instead of walking down a tree to figure out who gets to collect the money from whom, you should walk up the tree each time a new member is added to figure out who they pay money to. That means that for each new member you hit the database maybe half a dozen times instead of potentially thousands of hits walking down the tree.

Lol, GrandFather.

Pyramid schemes do exist, we are by far not one. your link has this definition of a pyramid scheme:
"A pyramid scheme is a non-sustainable business model that involves the exchange of money primarily for enrolling other people into the scheme, without any product or service being delivered."

We are a product based company, we only take on the best products that are brought to us, we have hundreds of products brought to use every month, the owner of the company only takes on the ones that are the best in their field. For instance, {Revised, not here to convince anyone or sell anything (removed rebuttle)}

Anyhow, I am not here trying to sell anything, I am just trying to populate the initial codings. I already have it to where it bases the rank on the person that referred them. So the problem I am having is only in the initial climbing down the tree. I start with each person ranked and then look at their whole referral tree(everyone in their personal array).


Thank you very much Rich

Try this: (Monks, please tell me if I am wrong assuming this would work) UnTested...

use vars qw/$reg_u_tbl $dbh $sth $rows $_autoField $_referField /;
$reg_u_tbl = "registered_users";
$_referField = "referrer_id";
$_autoField = "MemberId";
$_rankField = "ranked1";

$sth = $dbh->prepare("select * from `$reg_u_tbl` where ##RequirementsH
+ere##");
$sth->execute;
while($rows = $sth->fetchrow_hashref()) {
  my $thisMembersid = $rows->{$_autoField};
  my $referrersid = $rows->{$_referField};
  my $rankName = $rows->{$_rankField};
  my $startinglevel = 0;
  &buildTradRank ($thisMembersid,$referrersid,$rankName,$startinglevel
+);
}
$sth->finish();

sub buildTradRank {
    ($_rankedid,$_startingid,$_rank,$_level) = @_;
    my $_fieldName = $_rank . '_traditional';
    $_level++;
    $_countLevels++;
    $_highLevel = $_level if $_level > $_highLevel;
    
    if($_level == 1) {
        my $_addToSearch = "";
    } else {
        my $_addToSearch = qq~ and `$_rank` = "0"~;
    }
    $sth2 = $dbh->prepare(qq{select * from `$reg_u_tbl` where `$_refer
+Field` = ?$_addToSearch});
    $sth2->execute($_startingid);
    while(my $_nregu = $sth2->fetchrow_hashref()) {
        if(!$_nregu->{$_fieldName} || ($_nregu->{$_fieldName} && $_nre
+gu->{$_fieldName} != $_rankedid)) {
            my $_updated = $dbh->do(qq{update `$reg_u_tbl` set `$_fiel
+dName` = ? where `$_autoField` = ?}, undef, $_rankedid, $_nregu->{$_a
+utoField});
            if($_updated) {
                $_countAllNewlyAdded++;
            } else {
                $_countAllNewlyAddedFailures++;
                $_dbi_errors .= $DBI::errstr . "\n" if $DBI::errstr;
            }
        }
        if(!$_nregu->{$_rank}) {
            my $_countReferred = $dbh->selectrow_array(qq{select count
+(*) from `$reg_u_tbl` where `$_referField` = ? and `$_rank` = "0"}, u
+ndef, $_nregu->{MemberId});
            if(!$_countReferred) {
                next;
            }
            # Send back to this routine when it returns it should cont
+inue with the current data, starting it over SHOULD fork a new routin
+e...
            &buildTradRank($_rankedid,$_nregu->{$_autoField},$_rank,$_
+level);
        }
        next;
    }
  return 1;#Return when finished...
}
[download]

By creating one subroutine, you should be able to call it in the middle of it, which should start a new process, with new data and when it returns, then it should continue where it left off... You had it just about right, I only seen an error in your subroutine, you did not add the call to catch the data you passed to the subroutine...

Monks, am I correct in this?

Lee

Can you post your table structure, and how you define the relation between members ?

You might get a better solution at least to lower the database overhead (in your case if a member has 1016 referral, you'll have to do 1016 query which is not efficient).

I've taken a look at your code And I couldn't keep track of what's going on, Hopes someone else can help with your current code.

Here is a sample table format I use:

autoid bigint unsigned primary key auto_increment,
MemberId bigint unsigned,
referrer_id bigint unsigned,
...
[download]

each member has a unique MemberId, when they refer someone that person gets the referrers MemberId in the referrer_id field so for instance: You join, let's say your MemberId is 427890 (the next available id) and you refer John, John would have a record like this:

autoid	MemberId	referrer_id
427891	427891	427890



Then let's say John referrs Jane:

autoid	MemberId	referrer_id
427892	427892	427891



So you would have 2 levels of referrers, John and Jane, John is whom you referred, but we would not have Jane either if you did not refer John.

That is how I did it.


Thank you very much Rich

ukndoit

What about the ranked1 and ranked2 columns? It's all well and good to trim away unnecessary parts of tables, but don't forget to include everything required for the code. Also, provide enough sample data to help illustrate your specifications. For example, I'd provide at least one record for each "level" of rank or whatever the data hierarchy is that you need to track. Then I'd provide additional records so you can show why "Alice" would get infinity bonus points and why "Bob" doesn't.

...roboticus