I am working on a project that rewards people base on referrals (MLM)
I have been able to count the total number of child nodes on both left and right side but now I need to be able to update the ranks of users when they have a certain number of users with certain ranks below them on both sides. (I will explain better below:
User Table
id | name | parentID| side | rank |
4 | Dan | | | starter|
5 | Chris | 4 | left | starter|
6 | James | 4 | right| starter|
7 | Tybe | 5 | left | starter|
8 | Rose | 5 | right| starter|
9 | Paul | 6 | left | starter|
10 | Zach | 6 | right| starter|
Tree table
userID | left | right| leftCount | rightCount|
4 | 5 | 6 | 3 | 3 |
5 | 7 | 8 | 1 | 1 |
6 | 9 | 10 | 1 | 1 |
7 | | | 0 | 0 |
8 | | | 0 | 0 |
9 | | | 0 | 0 |
10 | | | 0 | 0 |
Below is the tree generated from this table
How i update the leftCount and rightCount when a user registers
$referralid; //who is referring the current user
$side; //the side the user is to fall under, either left or right
$temp_referralid = $referralid;
$temp_sideCount = $side.'Count'; //leftCount or rightCount
$temp_side = $side;
$total_count=1;
$i=1;
while($total_count>0){
$stmt = $db->prepare("SELECT * from tree WHERE id=:id");
$stmt->bindValue(':id', $temp_referralid);
$stmt->execute();
$r = $stmt->fetch(PDO::FETCH_ASSOC);
$current_temp_sideCount = ($r[$temp_sideCount]+1);
//This will add (+1) to the leftCount or rightCount
//of the referral depending on the side the user
//choose to fall under.
$sql ="UPDATE `tree` SET `$temp_sideCount`=:count WHERE `id` = :id";
$stmt = $db->prepare($sql);
$stmt->bindValue(':count', $current_temp_sideCount);
$stmt->bindValue(':id', $temp_referralid);
$stmt->execute();
//if the user has someone that referred them as
//only the last person at the top has no referral
if($temp_referralid!=""){
//change the $referralid to the person that referred the person
//referring this user. this is where the loop will go through
//to the last person maintaining the side upward
$next_referralid = $this->getReferringId($db, $temp_referralid);
$temp_side = $this->getReferringIdSide($db, $temp_referralid);
$temp_sideCount = $temp_side.'Count';
$temp_referralid = $next_referralid;
$i++;
}else{
$total_count=0;
}
}
}
Functions used
getReferringId($db, $id) //gets the referringId of the user passed as
//param from the user table
getReferringIdSide($db, $id) //gets the side which the user
//is on (left or right) from the user table
All this is working just fine but then I need to implement something and I haven’t found the best way around it.
I need to keep changing the rank for each user if they have attained a stage. see below and example:
stage 1: starter //just registered
stage 2: grow // the person has leftCount=3 and rightCount=3
stage 3: growbig //the person has 7 - grow user on the left
//and 7- grow user on the right
state 4: growbigger //the person has 7 - growbig on left and 7 growbig
//on the right
Up to stage 2, I have no problem but upwards is where I cant get my hands on the right logic
Update for example: The numbers of growbig's can come from anywhere on the legs, it shouldn’t just be direct nodes, just a count of ranks all below that user on each sides
UPDATE: Re-asking in a clearer term and specifications
its a binary tree (2:2) which means a person can only have two people directly under them (one on the left and another on the right.
With the picture above my table looks like this
Tree table
userID | left (userid) | right (userid)| rank
8 | 4 | 12 |
4 | 2 | 6 |
12 | 10 | 14 |
2 | 1 | 3 |
6 | 5 | 7 |
10 | 9 | 11 |
14 | 14 | 15 |
Note: its not compulsory for a user to have anyone under him on any side or both. it means if a user have nobody under him then the tree (branch) ends there, if he has one person directly on the left and none on the right it means the left branch can continue to grow.
The logic to grade each user base on their growths and how they have managed to balance the growth on each side is the problem.
The logic
Rank 1: supervisor: user must have at 3 users on its right branch and 3 users on the left branch.
Rank 2: controller: user must have 7 users who are 'supervisors' on it's left and 7 users who have become supervisors on the right too.
Rank 3: Senior Controller: user must have 7 users who are 'controller' on the left branch and have 7 'controller' on the right too.
Rank 4: Ambassador: user must have 7 users who are senior controller on its left and 7 senior controllers on the right
Rank 5: Senior Ambassador: user must have 7 users who are ambassadors on the left and same on the right.
Rank 6: Grand Ambassador: user must have 7 users who are senior ambassadors on his both sides.
Explanation: let me pick on rank and explain it:
Rank: Ambassador- if user with ID 3 has 45 people on its right hand side and 7 of the people on its right branch are senior controllers and also on the left he has 67 people and 7 are already senior controllers then user with ID 3 should be upgraded to 'ambassador'
@blag
<=2 childand do the update forgrowbig,growbiggeris exactly the same, just change the'grow'in the join. – Venterea