二进制树子计数php mysql

Question

I am stuck in some situation where I have to count Left Child and Right Child of a Binary Tree My database structure is as under.

SELECT id,usr_name,rid,pid,l_mem,r_mem,position,joining_date FROM user WHERE id = '$id'"

Where rid is = referral id And pid = parent id,

I need to count all leaves of given parent id for example

if id 1 has left 2 and right 3 immediate childs i need to know total left member count and total right members count.

                                         1
                                       /   \
                                     2       3
                                    / \     / \
                                   4   5   6   7
                                 /      \       \
                               8          9      11
                              /                   \
                            10                      12
                           /  \
                         13    14

I need to count all childs of 1. I am using this function but it is only counting most left please modified it or explain your own

  function leftcount($id)   //Function to calculate leftcount
  {
    $sql = "SELECT id,usr_name,rid,pid,l_mem,r_mem,position,joining_date FROM user WHERE id = '$id'";
    $execsql = mysql_query($sql);
    $array = mysql_fetch_array($execsql);
    //var_dump($array);
    (array_count_values($array));
    if(!empty($array['l_mem']))
    {
      $count += leftcount($array['l_mem']);  
    } 


    $totalcount = 1 + $count;
    return $totalcount  ;

  }

        $left = leftcount($id);
        doing -1 because in function 1 + $count.
        $left = $left-1;

Please do not mark Duplicate or any other if you don't have solution

Answer 1

You need to use these 3 function to calculate the left right and all child of any element.

function leftcount($id)   //Function to calculate all left children count
{
    $sql = "SELECT id,usr_name,rid,pid,l_mem,r_mem,position,joining_date FROM user WHERE id = '$id'";
    $execsql = mysql_query($sql);
    $array = mysql_fetch_array($execsql);
    (array_count_values($array));
    $count = 0;
    if(!empty($array['l_mem']))
    {
        $count += allcount($array['l_mem']) +1;
    }
    return $count;
}
function rightcount($id)   //Function to calculate all right children count
{
    $sql = "SELECT id,usr_name,rid,pid,l_mem,r_mem,position,joining_date FROM user WHERE id = '$id'";
    $execsql = mysql_query($sql);
    $array = mysql_fetch_array($execsql);
    (array_count_values($array));
    $count = 0;
    if(!empty($array['r_mem']))
    {
        $count += allcount($array['r_mem']) +1;
    }
    return $count;
}
function allcount($id)   //Function to calculate all children count
{
    $sql = "SELECT id,usr_name,rid,pid,l_mem,r_mem,position,joining_date FROM user WHERE id = '$id'";
    $execsql = mysql_query($sql);
    $array = mysql_fetch_array($execsql);
    (array_count_values($array));
    $count = 0;
    if(!empty($array['l_mem']))
    {
        $count += allcount($array['l_mem']) +1;
    }
    if(!empty($array['r_mem']))
    {
        $count += allcount($array['r_mem']) +1;
    }
    return $count;
}

If you pass 1 to these function. answer for these are as follows

echo leftcount(1); // 8
echo rightcount(1); // 5
echo allcount(1); // 13