Jump to content

Searching for a value in same geneology


Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 |

  , 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          |            |
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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 by Barand
Link to comment
Share on other sites

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



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.

Link to comment
Share on other sites

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



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.

Link to comment
Share on other sites

try this

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);
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.