Searching for a value in same geneology

[rubysymal]

I am making commission based level software. I mean it is like distributorship. If ’A’ joins the system as distributor and joins three members (B,C,D)  to the system then he should get extra 5% for every sales happening  under him or under B, C, D.  If suppose B also introduces three members( E, F, G) to the system under him then the 5% of every sales happening under B has to go to ‘B’ not to ‘A’. How can I write a coding for this in php? Here whenever a new member comes he has to check who is the one above him who is having the qualification and the 5% has to be given immediately to him automatically. Please help me in this problem.php.pdf

[ignace]

It's really simple. Add a referred_by to your table and when IS NOT NULL then apply 5% to the ID.

[rubysymal]

Thank you ignace, but what i am referring is only if a introduces 3 people then he should get 5%. if the person introduced by A ( eg: B) also introduces 3 people from under his geneology 5 % will go to B not to A. How to do it?

[rubysymal]

Suppose I am in 100th level this 5% has to go to only the person above me who qualified with introducing 5%. So i have to search back till I get an ID who has introduced 3 direct referrals. Is there any loop to do it. Or else can u just explain the coding?

[Barand]

do it with a query

mysql> SELECT * FROM test.refer;
+----+------+------------+
| id | name | referer_id |
+----+------+------------+
|  1 | A    |       NULL |
|  2 | B    |          1 |
|  3 | C    |          2 |
|  4 | D    |          3 |
|  5 | E    |          1 |
|  6 | F    |          2 |
|  7 | G    |          1 |
|  8 | H    |          3 |
|  9 | I    |          1 |
| 10 | J    |          3 |
| 11 | K    |          3 |
| 12 | L    |          4 |
+----+------+------------+

SELECT a.id
  , a.name
  , GROUP_CONCAT(b.name ORDER BY b.name SEPARATOR ', ') as referrals
  , IF(COUNT(b.name) > 2, '5%', '') as commission
FROM refer a 
	INNER JOIN refer b ON b.referer_id = a.id
GROUP BY a.id;
+----+------+------------+------------+
| id | name | referrals  | commission |
+----+------+------------+------------+
|  1 | A    | B, E, G, I | 5%         |
|  2 | B    | C, F       |            |
|  3 | C    | D, H, J, K | 5%         |
|  4 | D    | L          |            |
+----+------+------------+------------+

[rubysymal]

Thank You, But what I need is something else

 

1) Now on 16/07/2015 ID 1 (A) referred ID 2 (B), ID 3 © and ID 4 (D)

 

2) As he has referred 3 people he is eligible for a bonus 5% on any sales happening under him through him or through B, C AND D.

 

3) Now on 20/07/2015 ID 2 (B) also referred three people to business ID 5 (E), ID 6 (F) and ID 7 (G)

 

4) NOW ON WARDS what ever business happening under ID 2 (B) should fetch 5% only to ID 2 (B) and not to ID 1 (A).

 

5) ID 1 (A) will get 5% bonus from business happening under ID 3 © and ID 4 (D) till they also qualify by referring three persons each.

 

I am having a table called register in MYSQL. In that there is a column called user_rank. if 3 referrals are there for an ID the user_rank column will have value "YES"

 

When a new member joins the new Id has to see whether the up line or the person who sponsored him is having value YES in the user_rank column in register table. If not then it has to check whether the sponsor of his sponsor is having that , if not it has to check the sponsor's, sponsor's sponsor. This checking should go on till it finds an up line having a value YES inside the user_rank column . I am writing this coding in a php file. I need a php code for the same. Is there any loop which can go on checking rows up till the value is found. Please help me in this.

[Barand]

Why go to the trouble of maintaining that extra table with the YES flag when you can easily determine if a person has 3 referrals by querying the data you already have? Basic rule - do not store derived data.

 

ids with 3 or more:

SELECT referer_id
FROM refer
GROUP BY referer_id
HAVING COUNT(*) > 2;

Your search for sponsor's sponsor's sponsor will require a recursive function. Read the required data into an array for that otherwise it can get heavy on db server resources.

Edited July 16, 2015 by Barand

[QuickOldCar]

A while ago someone asked about a tier system and made a function for it.

http://forums.phpfreaks.com/topic/291591-wordpress-plugin-help/?do=findComment&comment=1492095

 

It may not be exactly what you need but can modify or get idea's from it.

 

I see it being useful to know the values needed to insert into the database or some calculating after a query.

[rubysymal]

Hi Mr.Barand,

 

            Thank you for your answer. I was also searching for a  recursive function. But I am not an expert in php regarding a  recursive function. So can you please show me how it look like in respect of the fields I am giving below

 

1) ID NO

2) Name

3) SPONSOR ID NO

 

Suppose I am in 100th level I need to go on checking up till I find an ID in my up lines who has qualified by introducing 3 directs. I want to fInd the person who is nearer to me above me in the genealogy AND HAS TO GIVE 5%. Please help me in this.

[Barand]

try this

<?php
include("db_inc.php"); // define HOST, USERNAME etc
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$sql = "SELECT id
        , name
        , r.referer_id
        , IF(num.referer_id IS NULL, 'NO', 'YES') as flag
        FROM refer r
        LEFT JOIN 
            (
            SELECT referer_id
            FROM refer
            GROUP BY referer_id
            HAVING COUNT(*) > 2
            ) num ON r.id = num.referer_id";

$people = array();
$res = $db->query($sql);
while (list($id,$name,$rid,$flag) = $res->fetch_row()) {
    $people[$id] = array($name,$rid,$flag);
}

$newId = 15;
$srch = hasSponsor3($newId, $people);         // call to recursive function
if ($srch !== false) {
    echo $srch;
}
else {
    echo "ID \"$newId\" does not exist";
}

function hasSponsor3($id, &$people)
/**
* Search people tree for any with 3+ referrals
* 
* @param $id - id of new person
* @param $people - array of $people
* @return id of sponsor, 0 if no sponsor found, false if error
*/
{
    if (!isset($people[$id]) ) return false;
    $referrer = $people[$id][1];
    if (!$referrer) {
        return 0;    // top of tree
    }
    if ($people[$referrer][2] == 'YES') {
        return $referrer;  // found a referrer with 3 or more referrals
    }
    else return hasSponsor3($referrer, $people);
}
?>