0

I've got various solution from SO to create my first MLM project, but now I got stuck in total down-line count (via recursive function), as I've no prior Idea on this, please anyone help me.

My Database Table structure is as below (table name member):

`member_id | member_name | node_left | node_right

where member's relation is as:

         member_id (Id 101)
               /\
              /  \
node_left(Id 102)  node_right(Id 103)
              /\                 /\
             /  \         blank /  \blank
(again) blank   node_right (104)

...... and so on. The above is just an example

`

Now I need to count total downline of any member. eg: suppose of above example, I want to know total downline of member_id 101 How to create the recursive function to do this which ends in finite loop ?

please give me any Idea..

Haren Sarma
  • 2,267
  • 6
  • 43
  • 72
  • The table structure shown (basically, it's an adjacency list design) isn't well suited for handling hierarchical data in SQL. See ["What are the Options for Storing Hierarchical Data in a Relational Database?"](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) for better alternatives. – outis Sep 23 '11 at 07:39
  • @outis: It seems to be a nested set design, not an adjacency list. – Rijk Sep 23 '11 at 08:47
  • @Rijk: each node stores an edge to its two children (the reverse of the standard schema used for adjacency, but adjacency nonetheless), not the bounds for the child trees. Note for the root you have `(101, '...', 102, 103)`; 104 is not in range, though it is a descendent. – outis Sep 23 '11 at 08:51
  • @outis: you seem to be right.. I've added an answer explaining the nested set model. – Rijk Sep 23 '11 at 08:54

2 Answers2

1

I'm not sure if you've tried to implement the nested set model here, but the implementation doesn't look right..

With a nested set, the left/right values representing your tree structure would look like this:

member 101 (root): left=1, right=8
member 102: left=2, right=5
member 103: left=3, right=4
member 104: left=6, right=7

Then, counting the childs of member #101 would be as simple as:

SELECT COUNT(*) FROM member WHERE node_left > 1 AND node_right < 8
Rijk
  • 11,032
  • 3
  • 30
  • 45
0

you can use/create your own customize function from the below code, Just check it out and try to implement for your case.

    function Recursive_getsubcategory($parent_id,$cat_group,$intLevel)

{
        global $intLevel;


    $sql = "select *,".$intLevel." as level from tbl_name where parent_id = '".$parent_id."' ";

    $result = mysql_query($sql);
    $cat_name = $result->fetch_array_multiple();

    if(count($cat_name) > 0)
    {   
        for($k=0;$k<count($cat_name);$k++)
        { 
            $cat_group[] = array('id'=>$cat_name[$k]['sub_id'],
                                        'parent_id'=>$cat_name[$k]['parent_id'],
                                        'level' =>  $cat_name[$k]['level']

                                        );

$parent_id[] = $cat_name[$k]['parent_id'];              

//Function For Recursive Get Sub Category...
Recursive_getsubcategory($cat_name[$k]['ebay_sub_id'],$cat_group,$intLevel++);
        }

    }
    // count of total downline nodes
return count($cat_group);

}

This code may helpful for your task.

Paresh Mayani
  • 127,700
  • 71
  • 241
  • 295
Chandresh M
  • 3,808
  • 1
  • 24
  • 48
  • The mysql extension is on its way to deprecation and shouldn't be used for new code. Rather than interpolating variables directly into statements, prepared statements should be used as they are safer and faster when repeating queries. Don't use [`SELECT *`](http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select); select only the columns you need. – outis Sep 23 '11 at 17:06
  • The [indentation](http://en.wikipedia.org/wiki/Indent_style) in the code is unreadable. `$intLevel` should not be declared global; it should only be passed as an argument. There's no need to add `$intLevel` to the `SELECT`. `mysql_query` returns a resource, not an object, and there is no `fetch_array_multiple` method. Incrementing `$intLevel` within the loop means subsequent siblings will be recorded at successively lower levels. – outis Sep 23 '11 at 17:08
  • At one point, you treat `$parent_id` as a scalar; at another, an array. The body of the loop performs a couple of unnecessary tasks: there's no need to record every parent ID, especially as they're all the same within an invocation of the function; there's no need to record every item in `$cat_group` since you only need the size. – outis Sep 23 '11 at 17:41