PHP计算二叉树中的下线数

Question

For calculating numbers of downline in binary tree, I am trying the following script by making 2 databases for registration and members downline structure. It works actually.

But the members structure database grow very fast. Caused n level in binary tree will generate n records for single user registration. I am just wondering if user register at level 1000 then it will create 1000 record in single user registration.

Any other solution for this system?

The full long script is :

CREATE TABLE IF NOT EXISTS `member` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `username` varchar(55) CHARACTER SET utf8 NOT NULL,
  `upline` varchar(55) CHARACTER SET utf8 NOT NULL,
  `position` varchar(10) NOT NULL,
  `sponsor` varchar(55) CHARACTER SET utf8 NOT NULL,
  `_left` varchar(55) CHARACTER SET utf8 NOT NULL,
  `_right` varchar(55) CHARACTER SET utf8 NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

make table of downline structure :

CREATE TABLE IF NOT EXISTS `net_downline` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `upline` varchar(60) NOT NULL,
  `level` int(7) NOT NULL,
  `position` varchar(10) NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

As get from simple or basic registration form :

$newuser        = htmlentities(trim($_POST['user']));
$sponsor        = htmlentities(trim($_POST['sponsor']));    
$upline         = htmlentities(trim($_POST['upline']));
$position       = htmlentities(trim($_POST['position']));

During registration process, this tree steps is executed :

// 1. register new member
$users->dummyRegister($newuser, $sponsor, $upline, $position);
// 2. update upline
$users->dummyUpdateUpline($newuser, $upline, $position);

// 3. create donwline structure of binary tree
$level=0;
$memberid=$newuser;
do{
$getdata=$users->dummyGetUpline($memberid); 
$uplne=$getdata[2]; 
$posi=$getdata[3];

$level++;
if($uplne!==''){
$users->dummyInsert_NetDownline($newuser, $uplne, $posi, $level);
}
$memberid=$uplne;
}
while($memberid!='');

class "user" for that script :

<?php  // start class

    class Users{

        private $db;
        public function __construct($database) {
            $this->db = $database;
        }   
    public function dummyRegister($username, $sponsor, $upline, $position, $today){

            $query  = $this->db->prepare("INSERT INTO `member` (`username`, `sponsor`, `upline`, `position`, `entry_date` ) VALUES (?, ?, ?, ?, ?) ");          
            $query->bindValue(1, $username);
            $query->bindValue(2, $sponsor);
            $query->bindValue(3, $upline);
            $query->bindValue(4, $position);
            $query->bindValue(5, $today);

            try{
                $query->execute();
             }catch(PDOException $e){
                die($e->getMessage());
            }   
        }


    public function dummyUpdateUpline($username, $upline, $position){

            if ($position=='left') {
            $query  = $this->db->prepare("UPDATE `member` SET `_left`=? WHERE username=? ");
            }elseif ($position=='right') {
            $query  = $this->db->prepare("UPDATE `member` SET `_right`=? WHERE username=? ");
            }

            $query->bindValue(1, $username);
            $query->bindValue(2, $upline);

            try{
                $query->execute();

            }catch(PDOException $e){
                die($e->getMessage());
            }   
        }

    public function dummyGetUpline($newuser) {// for demo

            $query = $this->db->prepare("SELECT * FROM `member` WHERE `username`= ?");
            $query->bindValue(1, $newuser);

            try{
                $query->execute();
                $rows = $query->fetch();

                return $rows;//['upline'];

            } catch(PDOException $e){
                die($e->getMessage());
            }
        }

    public function dummyInsert_NetDownline($newuser, $upline, $posi, $level){// for demo

            $query  = $this->db->prepare("INSERT INTO `net_downline` (`username`, `upline`, `position`, `level` ) VALUES (?, ?, ? ,?) ");
            $query->bindValue(1, $newuser);
            $query->bindValue(2, $upline);
            $query->bindValue(3, $posi);
            $query->bindValue(4, $level);

            try{
                $query->execute();

            }catch(PDOException $e){
                die($e->getMessage());
            }   
        }   

    }// endclass

create initial.php and put on top regitration php script :

<?php  
if(!isset($_SESSION)) { session_start(); }
require 'conn/database.php'; // in folder conn
require 'clas/users.php';   // in folder class
$users      = new Users($db);
} 
?>

this to handle database connection (clas/database.php script) :

<?php 
$config = array(
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => '',
    'dbname'    => 'sampledatabase'
);

$db = new PDO('mysql:host=' . $config['host'] . ';dbname=' . $config['dbname'], $config['username'], $config['password']);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

Answer 1

This is the answer after I try many ways. Solving my previous problem. Just using a single member table above.

To show number of downlines, left and right. I insert this script in the HTML member tree page for every user in the tree A, down to B/C, down to D/E/F/G ):

<?php echo $users->downline_number($member,'_left'); ?>
<?php echo $users->downline_number($member,'_right'); ?>

Add this function in User Class ;

function downline_number($member,$position) {

$query  = $this->db->prepare("SELECT * FROM `member` WHERE `upline`='$member' AND `position`='$position'");
        $query->bindValue(1, $member);
        $query->bindValue(2, $position);

try{
        $query->execute();
        $rows = $query->fetch();

        if($this->count_downline($member,$position) >0 ){
        $total=$this->total_members_down($rows['username']);
        }else{
        $total=0;
        }

        return $total;      

        }catch(PDOException $e){
            die($e->getMessage());
        }   

    }   

function count_downline($member,$position) {

$query  = $this->db->prepare("SELECT * FROM `member` WHERE `upline`=? AND `position`=? ");
        $query->bindValue(1, $member);
        $query->bindValue(2, $position);
    try{
        $query->execute();
        return $rows = $query->rowCount();

        }catch(PDOException $e){
            die($e->getMessage());
        }   
    }   

function total_members_down($upline,$reset=0) {
global $num;
if ($reset==0) { $num=1; }

$query  = $this->db->prepare("SELECT * FROM `member` where `upline`='$upline' order by id asc");
        $query->bindValue(1, $upline);
try{

$query->execute();

if ($upline !='') {

            if ($this->total_down($upline) > 0 ) {
                    while ($rows = $query->fetch() ) {
                    $num++;
                    $this->total_members_down($rows['username'],$num);
                    } 
                    return $num;
            } else { 
            return $num;
            }
} else { $num=0; return $num;  }            

     }catch(PDOException $e){
            die($e->getMessage());
        }   
}   

function total_down($upline) {

$query  = $this->db->prepare("SELECT * FROM `member` where `upline`='$upline' order by id asc ");
        $query->bindValue(1, $upline);

    try{
        $query->execute();
        return $rows = $query->rowCount();

        }catch(PDOException $e){
            die($e->getMessage());
        }   
    }   

and it works showing binary member tree structure. Showing the memberID is not attached here, caused it simple way. Just left & right downlines number.

Hope this post will help others who need it. Any suggestion for better ways?