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

Ok, I can see what you mean.

Here is a updated sample table format I use: (Ignore the autoid after this code, I will remove it as it is not pertinent to any of this)


autoid bigint unsigned primary key auto_increment,
MemberId bigint unsigned,
referrer_id bigint unsigned,
rank1 ENUM("0","1","2") DEFAULT "0" comment "1 when member acheives th
+is rank; 2 when temporarily does not qualify",
rank1_traditional BIGINT UNSIGNED DEFAULT "0",
ranked1 BIGINT UNSIGNED COMMENT "Member this person is coded to",
rank2 ENUM("0","1","2") DEFAULT "0" comment "1 when member acheives th
+is rank; 2 when temporarily does not qualify",
rank2_traditional BIGINT UNSIGNED DEFAULT "0",
ranked2 BIGINT UNSIGNED COMMENT "Member this person is coded to",
#...Other non related fields such as name and registration info...
[download]

Now each member has a unique MemberId, when they refer someone that person gets the referrers MemberId in the referrer_id field so for instance: Start with You for example

Level	MemberName	MemberId	referrer_id	rank1
0	You	427890	48	1



and you refer John, John would have a record like this:

Level	MemberName	MemberId	referrer_id	rank1
1	John	427891	427890	0



Then let's say John referrs Jane:(added only the rank1_traditional to show who this person is in the array of)

Level	MemberName	MemberId	referrer_id	rank1	rank1_traditional
2	Jane	427892	427891	0	427890



(added this next one), let me take it a little farther... Jane refers several...

Level	MemberName	MemberId	referrer_id	rank1	rank1_traditional
3	Tom	427893	427892	0	427890
3	Dick	427894	427892	0	427890
3	Harry	427895	427892	1	427890



4th Level small example:


Level	MemberName	MemberId	referrer_id	rank1	rank1_traditional
4	Bob	427896	427895	0	427895



So you would have 4 levels of referrers, John, Jane, Tom, Dick, Harry and Bob. John is whom you referred, but we would not have any of them if you did not refer John.

Also note that in the example Harry is a rank1 ranked member so Bob does not carry the MemberId from 'you', rather he carries Harry's MemberId since he is ranked as rank1. - This is why when I am climbing down the member tree, I check if he person I am looking at is ranked and if so, I do not go down their referral tree, because they will be looked at on their own in the very first loop because they are ranked...

We are like an advanced affiliate program, it is very close to MLM, but it is free to join and members get free retail websites several marketing sites and so forth and so on...

We compensate up to 60 cents on every dollar to our club members for referring the people they do. Once they achieve a certain predetermined number of personal referrals and those personal referrals achieve rank1 then the member is given rank2. There are actually 3 different rankings, but I am only showing the last two, as those are the only ones that are going to give bonuses on overrides.
Anyhow, So let me show you what I mean. When the club members have 30 people in the first 4 levels and at least 3 personally referred members whom have made a purchase at least in this calandar month or last calander month then they qualify to be at rank1. that is why there is a enum of 2 in the field because if somehow they are unqualified for a month or two, they can get qualified again.
These ranks were meant to reward them for all they do and give them something to work towards. So those that are rank1 then everyone they referred and that thoses people referred and that those ones did and so forth all the way to infinity referred will be coded to them in the traditional coded infinity which is rank1_traditional. That is to infinity until it reaches someone else that is ranked as rank1 because they will get everyone in their array below them coded to them.

I don't track levels, I only do that is I climb down for informational purposes, it is really irrelevent to this part. I have another program I wrote that does the formuals to see who qualifies for what and send them notices. This one just goes through and does the coding of them. It pays them once per month, at the beginning of every month for the last month. so that also is a different program.

I hope that gives you everything you asked for, please let me know if I missed something that would help.


Thank you very much Rich

ukndoit:

OK, I think I see where you're going. It appears that you want to compute a score for each member, where the score may depend on the score of other members. You might want to create a variation of a [no such wiki, Topological_sort], as it's fairly easy and lets you compute things in the database for speed. The essence of it is this: You'll make several passes over the table, and on each pass, you'll perform the computation for all members whose referrals are already computed. In pseudocode, it would look something like this:

# mark members with no referrals as done, an everyone else as not done
+.
# NOTE: I'm assuming a new column named is_done as a work column.  If 
+you prefer
# not to have such a column in your database, you can use a temporary 
+table instead.
$DB->do("update registered_users set is_done=0")
$DB->do("update registered_users set is_done=1 where MemberID not in (
+select referrer_id from registered_users");

while (1) {
   # Do we have any members that aren't done yet?
   my @tmp = $DB->selectrow_array("select count(*) from registered_use
+rs where is_done=0");
   last unless $tmp[0] > 0;

   # Perform computation for all members whose referrals are all "done
+"
   $DB->do(qq{
       UPDATE registered_users
       SET    is_done=1,
              <<<your calculation goes here>>>
       FROM registered_users
       WHERE is_done=0
       AND MemberID not in (
           SELECT referrer_id WHERE is_done=0
       )
   });
}
[download]

This way, you'll only need a few passes through the database to compute all your members, and you don't have to worry about recursive algorithms. Please note that this method can fail if your database has an error in it: If you have any loops in your database (e.g., Tom refers Dick who refers Tom or a longer loop) then the members in that loop, and any of their parents won't get computed. This shouldn't happen in your database, but if the method fails, you may want to look for an error in your data similar to this.

...roboticus