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.
rpkamp
June 29, 2018, 5:47am
2
What is the database schema you’ve come up with so far?
I have just created ‘members’ database table with columns like id name email contact location. Rest i am confused how should i proceed further.
So essentially you want a 2-level deep tree?
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
chorn
June 29, 2018, 9:21am
6
You have many choices to store hierarchical data, just a few:
adjacency list
nested set
path enumeration
SamA74
June 29, 2018, 5:19pm
7
I recall a while back someone posted this link in a topic which I bookmarked:-
Introduction Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database...
Est. reading time: 19 minutes
1 Like
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.
system
Closed
October 1, 2018, 8:51pm
9
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.