jsTree Multiple Tree MySQL/PHP implementation - Nested-sets

Question

I have implemented jsTree on my site with a php/MySQL back-end for tree storage and retrieval. I used the php/MySQL demo that came with the jsTree download for the basic infrastructure and then modified to my needs.

I have modified so that multiple trees can be stored in the same database, and added a new column of "owner_id" that stores the userid of the person that created that particular tree.

The php code that creates a new branch or moves a branch is not working correctly as it is not taking into account that there are multiple trees within the database.

jsTree uses the nested set model, and the script is adjusting the left and right values of all the trees in the database instead of just the one that has had a new branch added. This is slowly corrupting the entire database.

The following code shows the function/s that does the adjusting, could someone please try and amend the code for me so it uses the "owner_id" field to only make the changes to a particular tree?

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

Any help really appreciated.

Thanks

Surely you just need to add `owner_id` to each `WHERE` clause? — eggyal, Jan 21 '13 at 17:26
@eggyal - I thought so too, so I tried and it stopped adjusting the left and right values altogether. It just added the branch to the tree with the same left and right values as the previous branch of that tree. It did not adjust the values of the other trees though, so from that point of view it worked :) — superphonic, Jan 21 '13 at 17:32

score 0 · Accepted Answer · answered Jan 22 '13 at 17:13

For anyone else this might help, here is my code after the changes to I made to make creating / moving nodes only apply to the particular tree in question.

Adding the owner_id in the where clause did in fact fix the issue. I needed to add it to only certain queries:

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

Hope it helps someone...

Tushar Sawant · Answer 2 · 2023-08-21T06:32:16.487

-4

You want to modify the _move() function to consider the "owner_id" field when adjusting the nested set model. This way, changes will be made only to the specific tree associated with a given "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)

}

I've added the $owner_id parameter to both _create() and _move() functions and modified the SQL queries inside the _move()

edited Aug 21 '23 at 06:32

answered Sep 02 '19 at 16:19

Tushar Sawant

1
5

A link to a solution is welcome, but please ensure your answer is useful without it: [add context around the link](//meta.stackexchange.com/a/8259) so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. [Answers that are little more than a link may be deleted.](//stackoverflow.com/help/deleted-answers) – double-beep Sep 02 '19 at 16:36
Your profile indicates you are associated with the links you've included here. Linking to something you're affiliated with (e.g. a product or website) without disclosing that affiliation *in your post* is considered spam on Stack Exchange/Stack Overflow. See: [**What signifies "Good" self promotion?**](//meta.stackexchange.com/q/182212), [some tips and advice about self-promotion](/help/promotion), [What is the exact definition of "spam" for Stack Overflow?](//meta.stackoverflow.com/q/260638), and [What makes something spam](//meta.stackexchange.com/a/58035). – Makyen Sep 02 '19 at 16:44
Just a link to a your blog doesn't make for an answer on Stack Overflow. Answers must actually answer the question, without the requirement that the user click through to some other site to get the answer. Please [add context around links](//meta.stackoverflow.com/a/8259). **Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline.** Take into account that being _barely more than a link to an external site_ is a possible reason as to [Why and how are some answers deleted?](//stackoverflow.com/help/deleted-answers). – Makyen Sep 02 '19 at 16:44
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Aug 18 '23 at 23:35

jsTree Multiple Tree MySQL/PHP implementation - Nested-sets

2 Answers2