in reply to Recursive programming question

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.

Replies are listed 'Best First'.
Re^2: Recursive programming question
by ukndoit (Sexton) on May 09, 2010 at 11:57 UTC
    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:
    autoidMemberIdreferrer_id
    427891427891427890


    Then let's say John referrs Jane:
    autoidMemberIdreferrer_id
    427892427892427891


    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
[reply]
[d/l]

      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

[reply]
        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
[reply]
[d/l]

In Section Seekers of Perl Wisdom