SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    parse a database for referal program

    Hey guys.

    I hope this is in the right forum. I thought perhaps the mysql one, but I'm trying here - perhaps a mod will move it if need be.

    Ok... I have a database of users and when a user logs in, I want to be able to display people he has refered to the program for up to four levels...

    e.g.

    1st 2nd 3rd 4th

    me -> bob
    -> trevor -> ken
    -> phil -> john -> james

    (names don't match up to the code below and it doesn't have to appear like this. just an illustration )


    I have the following mysql table...

    CREATE TABLE `members` (
    `member_id` int(4) NOT NULL auto_increment,
    `name` varchar(40) NOT NULL,
    `username` varchar(40) NOT NULL,
    `password` varchar(40) NOT NULL,
    `mobile` varchar(20) NOT NULL default '123456',
    `email` varchar(50) NOT NULL default 'none@none.com',
    `date_joined` date NOT NULL,
    `refered_by` int(4) NOT NULL default '2',
    `active` char(1) NOT NULL default 'n',
    `plan` char(1) NOT NULL default 'b',
    PRIMARY KEY (`member_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    -- ----------------------------
    -- Records
    -- ----------------------------
    INSERT INTO `members` VALUES ('1', 'Admin', 'admin', 'admin', '0', 'admin', '2009-01-20', '0', 'n', 'b');
    INSERT INTO `members` VALUES ('2', 'dave', 'dave', 'dave', '0', 'dave', '2009-01-20', '0', 'n', 'b');
    INSERT INTO `members` VALUES ('29', 'james', 'james', 'james', '0', 'james', '2009-01-27', '2', 'n', 'b');
    INSERT INTO `members` VALUES ('30', 'tom', 'tom', 'tom', '0', 'tom', '2009-02-10', '2', 'n', 'b');
    INSERT INTO `members` VALUES ('31', 'phil', 'phil', 'phil', '0', 'phil', '2009-02-10', '2', 'n', 'b');
    INSERT INTO `members` VALUES ('32', 'john', 'john', 'john', '0', 'john', '2009-02-11', '30', 'n', 'b');
    INSERT INTO `members` VALUES ('33', 'bob', 'bob', 'bob', '0', 'bob', '2009-02-11', '30', 'n', 'b');
    INSERT INTO `members` VALUES ('34', 'ken', 'ken', 'ken', '0', 'ken', '2009-02-12', '33', 'n', 'b');
    INSERT INTO `members` VALUES ('35', 'steve', 'steve', 'steve', '0', 'steve', '2009-02-12', '34', 'n', 'b');
    INSERT INTO `members` VALUES ('36', 'andy', 'andy', 'andy', '0', 'andy', '2009-02-12', '35', 'n', 'b');



    Basically, a user logs in and then by their member_id I want to track who they have refered.

    I read the entire database into an array, but the iterative part is giving me real problems. Can anyone help with this, or point me to an example they may have done before? Happy to clarify if required.

    Many thanks, as ever!

    DS

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Dave recuited Tom, and then Tom went on and recruited John and Bob, so is the list of recruits supposed to be recursive to n levels?

    Export to an array? What will be the max number of rows your database will be holding then?
    100s? 1,000s? 10s of 1,000s?

  3. #3
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SirDaveTheBrave View Post
    I read the entire database into an array, but the iterative part is giving me real problems. Can anyone help with this, or point me to an example they may have done before? Happy to clarify if required.
    That doesn't sound very scalable. A basic algorithm would go something like:

    Code:
    $user := select * from user where user_id = $id
    $refer[1] := select * from user where refered_by = $user.id
    for ($level = 2 to 4):
      $refer[$level] := select * from user where refered_by in ($refer[$level - 1].map { |$user| $user.id })
    This is a fairly extensive (computing-wise) operation to make, so you probably want to throw in some caching, unless your database is very small.

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
      fir.name AS firstName, fir.refered_by AS firstReferer,
      sec.name AS secName, sec.refered_by AS secReferer,
      thi.name AS thirdName, thi.refered_by AS thirdReferer,
      fou.name AS FourthName, fou.refered_by AS fourthReferer
    FROM members AS fir
      INNER JOIN members AS sec
        ON sec.refered_by=fir.member_id
      INNER JOIN members AS thi
        ON thi.refered_by=sec.member_id
      INNER JOIN members AS fou
        ON fou.refered_by=thi.member_id
    which, with your datas gives me:
    Code:
    +-----------+--------------+---------+------------+-----------+--------------+------------+---------------+
    | firstName | firstReferer | secName | secReferer | thirdName | thirdReferer | FourthName | fourthReferer |
    +-----------+--------------+---------+------------+-----------+--------------+------------+---------------+
    | dave      |            0 | tom     |          2 | bob       |           30 | ken        |            33 |
    | tom       |            2 | bob     |         30 | ken       |           33 | steve      |            34 |
    | bob       |           30 | ken     |         33 | steve     |           34 | andy       |            35 |
    +-----------+--------------+---------+------------+-----------+--------------+------------+---------------+
    3 rows in set (0.00 sec)
    Which means that the complete chain is:
    dave -> tom -> bob -> ken -> steve -> andy

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys - thanks for taking the time to reply to me.

    Ok...
    I see what is happening here.

    Let me give you a more specific example.
    Let's say I log in as Dave.

    Then I would like my first level referals to display
    James, Tom and Phil

    Second Level referals should display as
    John, Bob (who were refered by James)

    Third Level referals should display as
    Ken (who was refered by Bob)

    Fourth Level referals should be displayed as
    Steve (refered by Ken)

    This is where the iteration should stop, because I am only concerned about the 4 levels immediately beneath the starting point... in this case, Dave. Andy would only feature in the results if we were starting further down the chain.

    This is why I initially looked at reading the database into an array... it is expected that the database will contain hundreds of people and I thought it would be more efficient to do all the tree-building from an array rather that a sql statement.

    Any more thoughts?


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
  •