SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question newbie needing help w/ php mysql - how best to track multi-tiered referral program

    I'm wondering what the best way to track users that a person refers(understanding that I am kind of a newb)

    Say for instance that User A refers User B who Refers Users C and D

    I want to be able to display:

    Level 1: User A
    Level 2: User B
    Level 3: User C, User D

    Now I have two ideas about how to go about this.
    Option 1:
    1 I could create a level 1, level 2, and level 3 field in table users.

    Field 1 would be: User A
    Field 2 would be User B
    Field 3 would be UserC~UserD

    Then I could take out the delimiter and such - but I was having trouble implementing this process....
    -------------------
    Option 2:
    This would be easier, from a newbie standpoint:

    Table: Tracking
    Fields: Sponsor, Ref, Level

    When a member registers it will create 10 new rows in tracking(I will be tracking 10 levels).
    E.G.:

    Row 1: Sponsor 1, Ref1, level 1
    Row 2: Sponsor 2, Ref1, level 2
    Row 3: Sponsor 3, Ref1, level 3
    Row 4: Sponsor 4, Ref1, level 4
    Row 5: Sponsor 5, Ref1, level 5
    .....etc...
    Then the next member:
    Row 6: Sponsor 1, Ref2, Level 1
    Row 7: Sponsor 2, Ref2, Level 2
    etc....
    Then to show the stats I would do mysql like so:

    Code:
    $result = mysql_query("SELECT ref FROM tracking WHERE sponsor='sponsor1' AND level='1'")
    or die(mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo $row['ref'];
    echo " | ";
    }
    Which would return: referral1 | referral 2

    I can see this creating a huge number of db entries, and am wondering if there's an easier way that I'm not thinking of. Would this be over-taxing to the DB?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The only data you need to store is who referred who (one column with the ID of a user's "sponsor").

    The hierarchy you get automatically by constructing an appropriate SELECT query.

    Joe's direct referrals:
    Code:
    SELECT
      user_id
    FROM
      users
    WHERE
      sponsor = 'joes user_id'
    Joe's second level referrals:
    Code:
    SELECT
      u2.user_id
    FROM
      users u1
    INNER JOIN
      users u2
        ON u2.sponsor = u1.user_id
    WHERE
      u1.user_id = 'joes user_id'
    Joe's third level referrals:
    Code:
    SELECT
      u3.user_id
    FROM
      users u1
    INNER JOIN
      users u2
        ON u2.sponsor = u1.user_id
    INNER JOIN
      users u3
        ON u3.sponsor = u2.user_id
    WHERE
      u1.user_id = 'joes user_id'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •