parse a database for referal program

[SirDaveTheBrave]

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

[Cups]

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?

[kyberfabrikken]

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:

$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.

[tripy]

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:

+-----------+--------------+---------+------------+-----------+--------------+------------+---------------+
| 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

[SirDaveTheBrave]

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?