Count Multi-level marketing (tree) record in php mysql

Question

users table

In the Registration time every user have to put parent_id , who registration under parent_id, so i made different table for it

sponser table

after that make tree like that

and i want to count record like that

so plz guide me how can i count record like that or there is any way i mean to say for this kind of counting i have to change in database , thanks in advance

Answer 1

i have Adjacency model structure in table, so i got it very nice solution count user_id under parent_id

that function count user_id under parent_id

function display_children($parent, $level) {
    $count = 0;
    $result = mysql_query('SELECT user_id FROM sponsers '.'WHERE parent_id="'.$parent.'"');
    while ($row = mysql_fetch_array($result))
    {
           $var = str_repeat(' ',$level).$row['user_id']."\n";

                   //echo $var  after remove comment check tree

                   // i call function in while loop until count all user_id 

           $count += 1 +$this->display_children($row['user_id'], $level+1);

    }
    return $count; // it will return all user_id count under parent_id
} 

call function

display_children(999, 0)