Complicated MySQL Query for MLM Business

Question

I've got one database to developed for MLM (Multi Level Marketing) company. There's one Members database.. each member have unique ID called membercode.. and all members have members under them.. Every member have 3 members under him/her.. and those 3 members can have same under them.. all members are added in same database table named tbMembers and for each member there's a parentID where I add member's membercode under whose that member is?.. right?

I want a SQL Query which can go under and under.. like 'John Doe' having three members under him named 'Suzan' and 'Ellie' and 'Smith'. And 'Suzan' is also having 3 members under her.. and same for Ellie. and for example.. 'John Doe' having about 300 members in his down line? and He gets paid for any member adds 3 members under his downline, that member adds 3 members also get paid and his upline members also get paid some amount of money. Can I get downline level number of any member under 1 particular member?? and can calculate his earnings based on 3 members joining under him.

I'm using PHP and use My-SQL. How to do it?

You have to use `recursive` . see [here][1] [1]: http://stackoverflow.com/questions/5725914/mysql-recursive-tree-search — Abadis, May 19 '13 at 06:53
I tried infinite recursive SQL query in one of my asp.net web app, but never tried this in php-mysql. my table structure is simple.. there is a table named tbMembers with columns (ID, parentID, memberCode, memberName, introducedBy, postedBy, postedOn).. Any member can't have more than 3 members under him.. If he wants add more members, then he must introduce them under any member under his downline. — idleMind, May 20 '13 at 05:43

score 2 · Answer 1 · answered May 19 '13 at 06:50

2

i dont know about your table structure but this is algorithme:

HERE is php code

    function get_recursive_users($pid)
    {
        $users = //get a query from users that parentid= $pid;
            /*for example db::query('SELECT * FROM `members` WHERE parentid="'.$parentid.'"');*/




        foreach($users as $user)
        {
            return get_recursive_users($user['pid']);
        }

    }

    $parentid = 'your current user Pid'
    get_recursive_users($parentid);

answered May 19 '13 at 06:50

babak faghihian

1,169
2
13
24

This code looks pretty easy! But It will slower the system I guess.. What you say?? If there are more than 1000 members under any user.. it can take time. – idleMind May 20 '13 at 05:44
this is about the lesson in university that named Algorithm Design in every way for scaling tree at least you should use recursive way – babak faghihian May 20 '13 at 06:10

score 0 · Answer 2 · answered May 22 '13 at 01:49

0

How about this one:

function get_downline_list($pid)
{
    /* query the database for all users ('SELECT * FROM `members` );*/
    foreach($row = mysql_fetch_assoc($result))
    {
       $allUsers[$row[parentID]][]=$row;
    }

    $dowlnineCount=1;
    $nextDownline=$allUsers[$pid];

    while($nextDownline && count($nextDownline)){
       $allDownlines[$dowlnineCount]=$nextDownline;

       foreach($nextDownline as $user){
           $anotherDownline[]=$user;
       }

       $nextDownlnine=$anotherDownline;
    }
}

I rushed because my laptop is running out of juice... I'll review the code in a few minutes.

answered May 22 '13 at 01:49

Marjeta

1,111
10
26

"in a few minutes" ... tumble weeds pass, for almost 9 years ;) – Adriaan Jan 09 '23 at 10:30
@Adriaan - You got me, I never came back to review this code... I haven't used PHP in ages. But if there's a need, I can brush up my PHP and review it. – Marjeta Jul 02 '23 at 23:57

score 0 · Answer 3 · answered Jun 27 '13 at 13:18

0

We have found other solution to quickly understand the chain of users.

Each member has 'path' field, consisting of users above him, including his id: '1,5,7,8,9,'

You need to generate this path for each new user. And we still use 'id' and 'pid'

answered Jun 27 '13 at 13:18

Slava Mashchenko

49
2

score -1 · Answer 4 · edited Jun 13 '21 at 16:33

I wrote this code and this returned me a valid data I wanted for my mlm software.

function getDlink($parent, $level){
    $users = $this->db->query('SELECT * FROM `member` WHERE parent_id="parent_id"')->result_array();
    static $arr = array();
    if ($users != null){
        foreach ($users as $user){
            $this->db->where('id', $user['id']);
            $u_data = $this->db->get('test')->row_array();
            array_push($arr, $user['id']);
            $parent_child = $user['id'];
            $this->getDlink($parent_child, 0);
        }
    }
    return $arr;
}

Complicated MySQL Query for MLM Business

4 Answers4