PHP计算二叉树中的下线数

Question

为了计算二叉树中的下线数量，我正在通过制作 2 个用于注册和成员下线结构的数据库来尝试以下脚本。它确实有效。

但是成员结构数据库增长非常快。导致二叉树中的n层会产生n条单用户注册记录。我只是想知道如果用户注册到 1000 级，那么它将在单个用户注册中创建 1000 条记录。

这个系统还有其他解决方案吗？

完整的长脚本是:

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 ;

制作下线结构表:

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 ;

从简单或基本的注册表中获取:

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

在注册过程中，执行树形步骤:

// 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!='');

该脚本的“用户”类:

<?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

创建 initial.php 并放在最上面 regitration php 脚本:

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

处理数据库连接(clas/database.php 脚本):

<?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

这是我尝试了很多方法后的答案。解决我之前的问题。仅使用上面的单个成员表。

显示左右下线的数量。我将此脚本插入到树 A 中每个用户的 HTML 成员树页面中，一直到 B/C，一直到 D/E/F/G):

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

在用户类中添加该函数；

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());
        }   
    }   

它可以显示二进制成员树结构。显示 memberID 没有附加在这里，导致它的简单方法。只是左右下线号码。

希望这篇文章能帮助到有需要的人。有什么更好的建议吗？