jsTree多树MySQL/PHP实现-嵌套集

Question

我已经在我的网站上使用了jsTree，并使用php/MySQL后端进行树形存储和检索。我使用了jsTree下载中带有的php/MySQL演示基础架构，然后按照我的需求进行了修改。

我对其进行了修改，使得可以在同一数据库中存储多个树，并添加了一个名为“owner_id”的新列，用于存储创建该特定树的用户ID。

由于它没有考虑到数据库中存在多个树，所以创建新分支或移动分支的php代码无法正常工作。

jsTree使用嵌套集模型，而脚本正在调整数据库中所有树木的左右值，而不仅仅是新添加了一个分支的树木。这将逐渐损坏整个数据库。

下面的代码显示了执行调整的函数，能否有人帮我修改代码，让它使用“owner_id”字段来仅更改特定的树？

function _create($parent, $position) {
    return $this->_move(0, $parent, $position);
}

and then...

function _move($id, $ref_id, $position = 0, $is_copy = false) {
    $hbhbhbh = fSession::get('nodes_allowed[nodes_access]');
    if ($hbhbhbh == "0" || $hbhbhbh == "2" || $hbhbhbh == "3") {
    if((int)$ref_id === 0 || (int)$id === 1) { return false; }
    $sql        = array();                      // Queries executed at the end
    $node       = $this->_get_node_ifuueuwyhddd($id);       // Node data
    $nchildren  = $this->_get_children($id);    // Node children
    $ref_node   = $this->_get_node_ifuueuwyhddd($ref_id);   // Ref node data
    $rchildren  = $this->_get_children($ref_id);// Ref node children

    $ndif = 2;
    $node_ids = array(-1);
    if($node !== false) {
        $node_ids = array_keys($this->_get_children($id, true));
        // TODO: should be !$is_copy && , but if copied to self - screws some right indexes
        if(in_array($ref_id, $node_ids)) return false;
        $ndif = $node[$this->fields["right"]] - $node[$this->fields["left"]] + 1;
    }
    if($position >= count($rchildren)) {
        $position = count($rchildren);
    }

    // Not creating or copying - old parent is cleaned
    if($node !== false && $is_copy == false) {
        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["position"]."` = `".$this->fields["position"]."` - 1 " . 
            "WHERE " . 
                "`".$this->fields["parent_id"]."` = ".$node[$this->fields["parent_id"]]." AND " . 
                "`".$this->fields["position"]."` > ".$node[$this->fields["position"]];
        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["left"]."` = `".$this->fields["left"]."` - ".$ndif." " . 
            "WHERE `".$this->fields["left"]."` > ".$node[$this->fields["right"]];
        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["right"]."` = `".$this->fields["right"]."` - ".$ndif." " . 
            "WHERE " . 
                "`".$this->fields["right"]."` > ".$node[$this->fields["left"]]." AND " . 
                "`".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ";
    }
    // Preparing new parent
    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["position"]."` = `".$this->fields["position"]."` + 1 " . 
        "WHERE " . 
            "`".$this->fields["parent_id"]."` = ".$ref_id." AND " . 
            "`".$this->fields["position"]."` >= ".$position." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");

    $ref_ind = $ref_id === 0 ? (int)$rchildren[count($rchildren) - 1][$this->fields["right"]] + 1 : (int)$ref_node[$this->fields["right"]];
    $ref_ind = max($ref_ind, 1);

    $self = ($node !== false && !$is_copy && (int)$node[$this->fields["parent_id"]] == $ref_id && $position > $node[$this->fields["position"]]) ? 1 : 0;
    foreach($rchildren as $k => $v) {
        if($v[$this->fields["position"]] - $self == $position) {
            $ref_ind = (int)$v[$this->fields["left"]];
            break;
        }
    }
    if($node !== false && !$is_copy && $node[$this->fields["left"]] < $ref_ind) {
        $ref_ind -= $ndif;
    }

    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["left"]."` = `".$this->fields["left"]."` + ".$ndif." " . 
        "WHERE " . 
            "`".$this->fields["left"]."` >= ".$ref_ind." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");
    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["right"]."` = `".$this->fields["right"]."` + ".$ndif." " . 
        "WHERE " . 
            "`".$this->fields["right"]."` >= ".$ref_ind." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");

    $ldif = $ref_id == 0 ? 0 : $ref_node[$this->fields["level"]] + 1;
    $idif = $ref_ind;
    if($node !== false) {
        $ldif = $node[$this->fields["level"]] - ($ref_node[$this->fields["level"]] + 1);
        $idif = $node[$this->fields["left"]] - $ref_ind;
        if($is_copy) {
            $sql[] = "" . 
                "INSERT INTO `".$this->table."` (" .
                    "`".$this->fields["parent_id"]."`, " . 
                    "`".$this->fields["position"]."`, " . 
                    "`".$this->fields["left"]."`, " . 
                    "`".$this->fields["right"]."`, " . 
                    "`".$this->fields["level"]."`" . 
                ") " . 
                    "SELECT " .
                        "".$ref_id.", " . 
                        "`".$this->fields["position"]."`, " . 
                        "`".$this->fields["left"]."` - (".($idif + ($node[$this->fields["left"]] >= $ref_ind ? $ndif : 0))."), " . 
                        "`".$this->fields["right"]."` - (".($idif + ($node[$this->fields["left"]] >= $ref_ind ? $ndif : 0))."), " . 
                        "`".$this->fields["level"]."` - (".$ldif.") " . 
                    "FROM `".$this->table."` " . 
                    "WHERE " . 
                        "`".$this->fields["id"]."` IN (".implode(",", $node_ids).") " . 
                    "ORDER BY `".$this->fields["level"]."` ASC";
        }
        else {
            $sql[] = "" . 
                "UPDATE `".$this->table."` SET " . 
                    "`".$this->fields["parent_id"]."` = ".$ref_id.", " . 
                    "`".$this->fields["position"]."` = ".$position." " . 
                "WHERE " . 
                    "`".$this->fields["id"]."` = ".$id;
            $sql[] = "" . 
                "UPDATE `".$this->table."` SET " . 
                    "`".$this->fields["left"]."` = `".$this->fields["left"]."` - (".$idif."), " . 
                    "`".$this->fields["right"]."` = `".$this->fields["right"]."` - (".$idif."), " . 
                    "`".$this->fields["level"]."` = `".$this->fields["level"]."` - (".$ldif.") " . 
                "WHERE " . 
                    "`".$this->fields["id"]."` IN (".implode(",", $node_ids).") ";
        }
    }
    else {
        $ewre = fSession::get('user[user_id]');
        $sql[] = "" . 
            "INSERT INTO `".$this->table."` (" .
                "`".$this->fields["owner"]."`, " . 
                "`".$this->fields["parent_id"]."`, " . 
                "`".$this->fields["position"]."`, " . 
                "`".$this->fields["left"]."`, " . 
                "`".$this->fields["right"]."`, " . 
                "`".$this->fields["level"]."` " . 
                ") " . 
            "VALUES (" .
                $ewre.", " .
                $ref_id.", " . 
                $position.", " . 
                $idif.", " . 
                ($idif + 1).", " . 
                $ldif. 
            ")";
    }
    foreach($sql as $q) { $this->db->query($q); }
    $ind = $this->db->insert_id();
    if($is_copy) $this->_fix_copy($ind, $position);
    return $node === false || $is_copy ? $ind : true;
    }
}

任何帮助都非常感激。谢谢。

- superphonic

---

你只需要在每个WHERE子句中添加owner_id，不就行了吗？ - eggyal

@eggyal - 我也这么想，所以我尝试了一下，结果它完全停止了调整左右值。它只是将该分支添加到树中，并使用与该树的上一个分支相同的左右值。但它没有调整其他树的值，所以从那个角度来看，它是有效的 :) - superphonic

Answer 1

如果有其他人需要帮助，这是我在更改后的代码，使创建/移动节点仅适用于特定的树。

在where子句中添加owner_id确实解决了问题。我只需要将其添加到特定的查询中：

function _move($id, $ref_id, $position = 0, $is_copy = false) {
    $hbhbhbh = fSession::get('nodes_allowed[nodes_access]');
    if ($hbhbhbh == "0" || $hbhbhbh == "2" || $hbhbhbh == "3") {
    if((int)$ref_id === 0 || (int)$id === 1) { return false; }
    $sql        = array();                      // Queries executed at the end
    $node       = $this->_get_node_ifuueuwyhddd($id);       // Node data
    $nchildren  = $this->_get_children($id);    // Node children
    $ref_node   = $this->_get_node_ifuueuwyhddd($ref_id);   // Ref node data
    $rchildren  = $this->_get_children($ref_id);// Ref node children


    $ndif = 2;
    $node_ids = array(-1);

    if($node !== false) {


        $node_ids = array_keys($this->_get_children($id, true));
        // TODO: should be !$is_copy && , but if copied to self - screws some right indexes
        if(in_array($ref_id, $node_ids)) return false;
        $ndif = $node[$this->fields["right"]] - $node[$this->fields["left"]] + 1;
    }

    if($position >= count($rchildren)) {        

        $position = count($rchildren);
    }


    // Not creating or copying - old parent is cleaned
    if($node !== false && $is_copy == false) {


        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["position"]."` = `".$this->fields["position"]."` - 1 " . 
            "WHERE " .
                "`".$this->fields["owner"]."` = ".(int) $node[$this->fields["owner"]]." AND " .
                "`".$this->fields["parent_id"]."` = ".$node[$this->fields["parent_id"]]." AND " . 
                "`".$this->fields["position"]."` > ".$node[$this->fields["position"]];
        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["left"]."` = `".$this->fields["left"]."` - ".$ndif." " . 
            "WHERE `".$this->fields["left"]."` > ".$node[$this->fields["right"]]." AND " .
                "`".$this->fields["owner"]."` = ".(int) $node[$this->fields["owner"]];
        $sql[] = "" . 
            "UPDATE `".$this->table."` " . 
                "SET `".$this->fields["right"]."` = `".$this->fields["right"]."` - ".$ndif." " . 
            "WHERE " .
                "`".$this->fields["owner"]."` = ".(int) $node[$this->fields["owner"]]." AND " .
                "`".$this->fields["right"]."` > ".$node[$this->fields["left"]]." AND " . 
                "`".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ";
    }



    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["position"]."` = `".$this->fields["position"]."` + 1 " . 
        "WHERE " . 
            "`".$this->fields["parent_id"]."` = ".$ref_id." AND " . 
            "`".$this->fields["position"]."` >= ".$position." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");



    $ref_ind = $ref_id === 0 ? (int)$rchildren[count($rchildren) - 1][$this->fields["right"]] + 1 : (int)$ref_node[$this->fields["right"]];
    $ref_ind = max($ref_ind, 1); 


    $self = ($node !== false && !$is_copy && (int)$node[$this->fields["parent_id"]] == $ref_id && $position > $node[$this->fields["position"]]) ? 1 : 0;

    foreach($rchildren as $k => $v) {


        if($v[$this->fields["position"]] - $self == $position) {
            $ref_ind = (int)$v[$this->fields["left"]];
            break;
        }
    }


    if($node !== false && !$is_copy && $node[$this->fields["left"]] < $ref_ind) {



        $ref_ind -= $ndif;
    }

    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["left"]."` = `".$this->fields["left"]."` + ".$ndif." " . 
        "WHERE " . 
            "`".$this->fields["owner"]."` = ".(int) $ref_node[$this->fields["owner"]]." AND `".$this->fields["left"]."` >= ".$ref_ind." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");
    $sql[] = "" . 
        "UPDATE `".$this->table."` " . 
            "SET `".$this->fields["right"]."` = `".$this->fields["right"]."` + ".$ndif." " . 
        "WHERE " . 
            "`".$this->fields["owner"]."` = ".(int) $ref_node[$this->fields["owner"]]." AND `".$this->fields["right"]."` >= ".$ref_ind." " . 
            ( $is_copy ? "" : " AND `".$this->fields["id"]."` NOT IN (".implode(",", $node_ids).") ");


    $ldif = $ref_id == 0 ? 0 : $ref_node[$this->fields["level"]] + 1;
    $idif = $ref_ind;


    if($node !== false) {


        $ldif = $node[$this->fields["level"]] - ($ref_node[$this->fields["level"]] + 1);
        $idif = $node[$this->fields["left"]] - $ref_ind;
        if($is_copy) {


            $sql[] = "" . 
                "INSERT INTO `".$this->table."` (" .
                    "`".$this->fields["parent_id"]."`, " . 
                    "`".$this->fields["position"]."`, " . 
                    "`".$this->fields["left"]."`, " . 
                    "`".$this->fields["right"]."`, " . 
                    "`".$this->fields["level"]."`" . 
                ") " . 
                    "SELECT " .
                        "".$ref_id.", " . 
                        "`".$this->fields["position"]."`, " . 
                        "`".$this->fields["left"]."` - (".($idif + ($node[$this->fields["left"]] >= $ref_ind ? $ndif : 0))."), " . 
                        "`".$this->fields["right"]."` - (".($idif + ($node[$this->fields["left"]] >= $ref_ind ? $ndif : 0))."), " . 
                        "`".$this->fields["level"]."` - (".$ldif.") " . 
                    "FROM `".$this->table."` " . 
                    "WHERE " . 
                        "`".$this->fields["id"]."` IN (".implode(",", $node_ids).") " . 
                    "ORDER BY `".$this->fields["level"]."` ASC";
        }
        else {
            $sql[] = "" . 
                "UPDATE `".$this->table."` SET " . 
                    "`".$this->fields["parent_id"]."` = ".$ref_id.", " . 
                    "`".$this->fields["position"]."` = ".$position." " . 
                "WHERE " . 
                    "`".$this->fields["id"]."` = ".$id;
            $sql[] = "" . 
                "UPDATE `".$this->table."` SET " . 
                    "`".$this->fields["left"]."` = `".$this->fields["left"]."` - (".$idif."), " . 
                    "`".$this->fields["right"]."` = `".$this->fields["right"]."` - (".$idif."), " . 
                    "`".$this->fields["level"]."` = `".$this->fields["level"]."` - (".$ldif.") " . 
                "WHERE " . 
                    "`".$this->fields["id"]."` IN (".implode(",", $node_ids).") ";
        }

    } else {


        $ewre = fSession::get('user[user_id]');
        $sql[] = "" . 
            "INSERT INTO `".$this->table."` (" .
                "`".$this->fields["owner"]."`, " . 
                "`".$this->fields["parent_id"]."`, " . 
                "`".$this->fields["position"]."`, " . 
                "`".$this->fields["left"]."`, " . 
                "`".$this->fields["right"]."`, " . 
                "`".$this->fields["level"]."` " . 
                ") " . 
            "VALUES (" .
                $ewre.", " .
                $ref_id.", " . 
                $position.", " . 
                $idif.", " . 
                ($idif + 1).", " . 
                $ldif. 
            ")";
    }


    foreach($sql as $q) { $this->db->query($q); }
    $ind = $this->db->insert_id();
    if($is_copy) $this->_fix_copy($ind, $position);
    return $node === false || $is_copy ? $ind : true;
    }
}

希望它能对某人有所帮助...

- superphonic

Answer 2

你想修改_move()函数，在调整嵌套集模型时考虑"owner_id"字段。这样，只有与给定"owner_id"相关联的特定树才会进行更改。

function _create($parent, $position, $owner_id) {
    return $this->_move(0, $parent, $position, false, $owner_id);
}

function _move($id, $ref_id, $position = 0, $is_copy = false, $owner_id) {
// ... (your existing code)

// Create a PDO instance
$pdo = new PDO("mysql:host=your_host;dbname=your_database", "your_username", "your_password");

// Preparing new parent
$stmt = $pdo->prepare("
    UPDATE `" . $this->table . "` 
    SET `" . $this->fields["position"] . "` = `" . $this->fields["position"] . "` + 1 
    WHERE 
        `" . $this->fields["parent_id"] . "` = :ref_id AND 
        `" . $this->fields["position"] . "` >= :position " . 
        ( $is_copy ? "" : " AND `" . $this->fields["id"] . "` NOT IN (".implode(",", $node_ids).") ") . 
        " AND `" . $this->fields["owner"] . "` = :owner_id"
);

// Bind parameters
$stmt->bindParam(':ref_id', $ref_id, PDO::PARAM_INT);
$stmt->bindParam(':position', $position, PDO::PARAM_INT);
$stmt->bindParam(':owner_id', $owner_id, PDO::PARAM_INT);

// Execute the query
$stmt->execute();

// ... (your existing code)

// Continue using prepared statements for the other queries in a similar manner.

// ... (your existing code)

// Close the database connection
$pdo = null;

// ... (your existing code)

}

我已经在_create()和_move()函数中添加了$owner_id参数，并修改了_move()内部的SQL查询。

- Tushar Sawant

---

欢迎提供解决方案的链接，但请确保您的回答在没有链接的情况下也是有用的：在链接周围添加一些上下文，以便其他用户了解它是什么以及为什么存在，然后引用您链接到的页面中最相关的部分，以防目标页面无法访问。只包含链接的答案可能会被删除。 - double-beep

您的个人资料显示您与您在此处提供的链接有关联。在未在帖子中披露此关联的情况下，将链接到您所属的内容（例如产品或网站）被视为 Stack Exchange/Stack Overflow 上的垃圾邮件。请参阅：什么是“良好”的自我推广？，关于自我推广的一些建议和提示，Stack Overflow 对“垃圾邮件”的确切定义是什么？，以及什么会被视为垃圾邮件。 - Makyen

只是在Stack Overflow上提供一个链接到你的博客并不能构成一个答案。答案必须实际回答问题，而不需要用户点击其他网站来获取答案。请在链接周围添加上下文。始终引用重要链接的最相关部分，以防目标网站无法访问或永久离线。请考虑到“仅仅是一个指向外部网站的链接”可能是为什么和如何删除某些答案的原因之一。 - Makyen

警告：您的系统存在严重的SQL注入风险，应该使用参数化的预处理语句来构建查询，而不是手动拼接。这些功能可以通过PDO或者MySQLi提供。无论输入来自何方，请勿轻信！即使您的查询只由可信用户执行，仍然有可能破坏数据的完整性。转义并不足够安全！ - Dharman