Multi Level Nested Referral system in php and mysql

PHP
1

I have a members system ready with basic details in mysql like

id
name
email
contact

I want to now create a multilevel system in this. Like if A referred B and B referred C and C referred D, then A’s referred members should show both B and C, whereas B’s referred members should show C & D. But C’s referred members should show only D.

I am using Mysql and unable to show both B and C in A. Please help me with this.

2

What is the database schema you’ve come up with so far?

3

I have just created ‘members’ database table with columns like id name email contact location. Rest i am confused how should i proceed further.

4

So essentially you want a 2-level deep tree?

5

Basically yes. But it will go on and on.

If A refers B and B refers C and C refers D and D refers E then i want B, C, D, E all of them to be showed as referrals of A

6

You have many choices to store hierarchical data, just a few:

  • adjacency list
  • nested set
  • path enumeration
7

I recall a while back someone posted this link in a topic which I bookmarked:-

1 Like
8

You mean something like this?

SQL:

CREATE TABLE `refs` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`person` VARCHAR(20) NOT NULL,
	`referer_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `refs` (`person`, `referer_id`) VALUES ('A', NULL);
INSERT INTO `refs` (`person`, `referer_id`) VALUES ('B', 1);
INSERT INTO `refs` (`person`, `referer_id`) VALUES ('C', 2);
INSERT INTO `refs` (`person`, `referer_id`) VALUES ('D', 3);

PHP:

error_reporting(-1);
$pdo = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpass');
$refs = $pdo->query("SELECT * FROM refs")->fetchAll(PDO::FETCH_ASSOC);

foreach ($refs as $ref) {
    echo $ref['person'] . ' refered members:<br/>';
    echo getRefs($refs, $ref['id']);
    echo '<br/><br/>';
}

function getRefs($array, $parent = 0, $level = 0) {
    $referedMembers = '';
    foreach ($array as $entry) {
        if ($entry['referer_id'] === $parent) {
            $referedMembers .= '- ' . $entry['person'] . '<br/>';
            $referedMembers .= getRefs($array, $entry['id'], $level+1);
        }
    }
    return $referedMembers;
}

Person data should probably be at its own table, this is just a simplified example.

If you only want to go down 2 levels of referrals per user like your original message suggest you could add if ($level === 2) break; in the foreach loop of getRefs function.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.