PHP 移动 mySQL 树节点 [英] PHP Moving mySQL Tree Node

问题描述

我在尝试向上或向下移动子节点或父节点时遇到问题......数学不太好.

<块引用>

CREATE TABLE IF NOT EXISTS `pages` ( `page-id` mediumint(8) unsigned

NOT NULL AUTO_INCREMENT, page-leftmediumint(8) 无符号非空，
page-right smallint(8) 无符号 NOTNULL, page-title text NOT NULL,
page-content text NOT NULL,
page-time int(11) unsigned NOT NULL,page-slug text NOT NULL,
page-template text NOT NULL,
page-parent mediumint(8) 无符号NOT NULL, page-type 文本 NOT NULL,主键 (page-id) )引擎=MyISAM 默认字符集=latin1;

INSERT INTO pages (page-id,page-left, page-right,page-title, page-content,page-time, page-slug,page-template, page-parent,page-type) VALUES (17, 1, 6, '1','', 0, '父', '', 0, ''), (18, 2,5, '2', '', 0, 'SUB', '', 17, ''),(19, 3, 4, '3', '', 0, 'SUB-SUB', '',18, ''), (20, 7, 8, '5', '', 0,'测试', '', 0, '');

例如，我如何将 TEST 向上移动到 PARENT 上方，并说通过使用左页/右页 ID 将 SUB 向下移动到 SUB-SUB 下方?不需要代码，只需要 SQL 概念或数学方面的帮助，就能帮助我理解如何更好地移动它...

解决方案

所以基本上您想将邻接列表转换为嵌套集?首先更新您的邻接列表(即将 page_parent 值更新为新树的正确值)，然后运行下面的转换.

使用 PHP(基本代码，未经测试):

类树{私人 $count = 0;私人 $data = array();/*** 重建嵌套集** @param $rawData array 原始树数据*/公共函数重建($rawData){$this->data = $rawData;$this->count = 1;$this->traverse(0);}私有函数遍历($id){$lft = $this->count;$this->count++;如果 (isset($this->data[$id])) {$kid = $this->data[$id];如果($孩子){foreach ($kid as $c) {$this->traverse($c);}}}$rgt = $this->count;$this->count++;//TODO: 将左右值更新为 $lft &$rgt 在您的数据库中为 page_id $id...}}

当你调用它时，$rawData 应该包含一个 ID 数组，由父 ID 索引，你可以创建它(基于你的表结构)如下($db 应该包含一个活动的 PDO 连接对象):

 $sql = 'SELECT page_id, page_parent FROM pages ORDER BY page_parent';$stmt = $db->prepare($sql);$rawData = 数组();$stmt->execute();while ($row = $stmt->fetch()) {$parent = $row['page_parent'];$child = $row['page_id'];如果 (!array_key_exists($parent, $rawData)) {$rawData[$parent] = array();}$rawData[$parent][] = $child;}

要进行转换，您需要类似于:

$tree = new Tree();$tree->rebuild($rawData);

因此，基本上您创建一个数组，其中包含由父索引索引的树中的所有节点，这些节点将以递归方式遍历以确定每个节点的正确左值和右值.

顺便说一句，你可以用普通的 SQL 来完成(在你调整表/列名之后):http://bytes.com/topic/mysql/answers/638123-regenerate-nested-set-using-parent_id-structure

I am having trouble trying to move sub nodes or parent nodes up or down... not that good at math.

CREATE TABLE IF NOT EXISTS `pages` (   `page-id` mediumint(8) unsigned

NOT NULL AUTO_INCREMENT, page-left mediumint(8) unsigned NOT NULL,
page-right smallint(8) unsigned NOT NULL, page-title text NOT NULL,
page-content text NOT NULL,
page-time int(11) unsigned NOT NULL, page-slug text NOT NULL,
page-template text NOT NULL,
page-parent mediumint(8) unsigned NOT NULL, page-type text NOT NULL, PRIMARY KEY (page-id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

INSERT INTO pages (page-id, page-left, page-right, page-title, page-content, page-time, page-slug, page-template, page-parent, page-type) VALUES (17, 1, 6, '1', '', 0, 'PARENT', '', 0, ''), (18, 2, 5, '2', '', 0, 'SUB', '', 17, ''), (19, 3, 4, '3', '', 0, 'SUB-SUB', '', 18, ''), (20, 7, 8, '5', '', 0, 'TEST', '', 0, '');

As example how would I move TEST up above PARENT and say move SUB down below SUB-SUB by playing with the page-left/page-right IDs? Code is not required just help with the SQL concept or math for it, would help me understand how to move it better...

解决方案

So basically you want to convert an adjacency list to a nested set? First update your adjacency list (ie. update the page_parent values to the correct values for your new tree), then run the conversion below.

Using PHP (basic code, untested) :

class Tree
{    
    private $count = 0;
    private $data = array();

    /**
     * Rebuild nested set
     * 
     * @param $rawData array Raw tree data
     */
    public function rebuild($rawData)
    {
        $this->data = $rawData;
        $this->count = 1;
        $this->traverse(0);        
    }

    private function traverse($id)
    {
        $lft = $this->count;
        $this->count++;

        if (isset($this->data[$id])) {
            $kid = $this->data[$id];
            if ($kid) {
                foreach ($kid as $c) {
                    $this->traverse($c);
                }
            }
        }

        $rgt = $this->count;
        $this->count++;

        // TODO: Update left and right values to $lft & $rgt in your DB for page_id $id
        ...
    }
}

When you call this, $rawData should contain an array of ID's, indexed by parent-id, you could create it (based on your table structure) as follows ($db should contain an active PDO connection object):

    $sql = 'SELECT page_id, page_parent FROM pages ORDER BY page_parent';

    $stmt = $db->prepare($sql);
    $rawData = array();
    $stmt->execute();
    while ($row = $stmt->fetch()) {
        $parent = $row['page_parent'];
        $child = $row['page_id'];
        if (!array_key_exists($parent, $rawData)) {
            $rawData[$parent] = array();
        }
        $rawData[$parent][] = $child;
    }

To do the conversion you would need something like :

$tree = new Tree();
$tree->rebuild($rawData);

So basically you create an array that is containing all nodes in your tree indexed by parent which will be traversed in a recursive manner to determine the correct left and right values per node.

BTW You could do it in plain SQL (after you adapt table/column names) : http://bytes.com/topic/mysql/answers/638123-regenerate-nested-set-using-parent_id-structure

这篇关于PHP 移动 mySQL 树节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！