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

Answer 1

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

Answer 2

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