Copy folder and file structure in the database

[developer707]

Hello to everyone,
Currenty I have a php project that uploads files to the server and save some extra information into the database.
My problem is that I want to make a feature that copies a folder structure with files included to a database.

I am trying to copy the folder structure, but the issue is that I cant follow the relationships with the new ids.

The main structure of the table is like this

CREATE TABLE `people_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) NOT NULL COMMENT 'Here is stored the root folder id',
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `people_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) NOT NULL COMMENT 'Here is stored the id of the folder that the file is stored',
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

To be more clear, I am trying to do something that Joomla! do copy menus
https://docs.joomla.org/images/8/81/Help30-colheader-batch-process-articles.png

So you're using an adjacency list model (self-referencing 'parent id' field) for your folders.

Not sure where the relashionships / referencing is going wrong for you. Is it within the people_folders table or between people_folders>id and people_documents>folder_id?

Your column names are a little confusing. Personally, I'd rename them to something more descriptive:

CREATE TABLE `people_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL COMMENT 'Here is stored the root folder id',
  `folder_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `people_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) NOT NULL COMMENT 'Here is stored the id of the folder that the file is stored',
  `file_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Is this just to show the table structure? Or are you running this when you want to copy the structure? Am a bit confused as an .sql file containing the CREATE statements and valid bulk INSERT should 'duplicate' with no problem.

This works for me (using the column names I suggested):

DROP TABLE IF EXISTS `people_documents`;
`people_folders`
CREATE TABLE `people_documents` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `folder_id` INT(11) DEFAULT NULL,
  `file_name` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `people_documents` */

INSERT  INTO `people_documents`(`id`,`folder_id`,`file_name`) VALUES (1,2,'about.php'),(2,2,'contact.php'),(3,2,'home.php'),(4,3,'main.css'),(5,4,'jquery.js'),(6,4,'jqueryui.js'),(7,5,'me.gif'),(8,5,'map.jpg'),(9,5,'logo.png'),(10,6,'rugby.mp4'),(11,7,'header.php'),(12,7,'menu.php'),(13,8,'login.php'),(14,8,'register.php'),(15,9,'serene.html'),(16,9,'urbane.html');

/*Table structure for table `people_folders` */

DROP TABLE IF EXISTS `people_folders`;

CREATE TABLE `people_folders` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT(11) DEFAULT NULL,
  `folder_name` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `people_folders` */

INSERT  INTO `people_folders`(`id`,`parent_id`,`folder_name`) VALUES (1,0,'assets'),(2,0,'pages'),(3,1,'css'),(4,1,'js'),(5,1,'images'),(6,1,'media'),(7,2,'sections'),(8,3,'forms'),(9,0,'templates');

N.B. If you have constraints / FKs, then you may need to swap the order of the tables/data.

[developer707]

The folder_id tells the id of the parent folder, if it is 0 is a root folder
On this post I am posting just the columns that for me are essential for the procedure
An example of the rows
people_folders
1, 0, Main Folder
2, 0, Main Folder II
3, 1, Subfolder I --This folder is under Main Folder*
4, 3, Subfolder III --This folder is under Subfolder I*

people_documents
1, 3, File I
2, 3, File II
3, 4, File III

For example if I copy Main Folder with ID = 1 to Main Folder II the information on the database would be this

people_folders
1, 0, Main Folder
2, 0, Main Folder II
3, 1, Subfolder I --This folder is under Main Folder*
4, 3, Subfolder III --This folder is under Subfolder I*

5, 2, Main Folder
6, 5, Subfolder I
7, 6, Subfolder III

people_documents
1, 3, File I
2, 3, File II
3, 4, File III

4, 6, File I
5, 6, File II
6, 7, File III

Hold on. You're talking about updating the DB not just creating a new copy of it. You didn't make this clear.

This is a pig of a way to do it. It looks like you need recursive function as you follow each branch to its end. Urgh. I'll have a think tomorrow. Hopefully others will get back to you in the meantime.

[developer707]

Yes diafol,
Maybe my explanation was not very clear in the begining.
My issue is with the new id's of the folders that are copy/pasted(select/inserted),
as you may be clear now its somehow complicated.

Days before I have seen an example to doing this the recursive function, but I am not finding that anymore...

Also I want to thank you anyway, because in most of my issues you have saved me :)

Had a thought about it and I really think you should be using the nested sets model (NSM) and the modified preorder tree traversal algorithm rather than the adjacency list model (ALM). Trying to write SQL for ALM is hideous. I've seen solutions and they are pretty insane. But that's not the only issue, for this to work you'll need a 'level' or 'depth' field too, which you don't have at the moment.

The NSM is much simpler to store anyway. Please read this - it's really beautiful: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

I am testing a solution at the moment - looking good so far.

[developer707]

I have seen the hierarchical method is better then the structure that I am using, but I can't understand how it works. How lft and rgt are calculated when you enter a new record on the table.

[jkon]

Hello Klaus , first the code and then some comments

<?php
error_reporting(E_ALL);
ini_set("display_errors", "1");

$database = "test";
$username = "testuser";
$password = "testpassword";

// Nowdays MySQL hase natural prepared statements and
// we gain from using them and not the PDO emulation of them
$pdoOptions = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

// You could use also try catch when you do with Db
$db = new Db("mysql:host=localhost;dbname=".$database
, $username, $password,$pdoOptions);

$operation = new Model_Operations_CopyFolder($db);
$operation->copyFolder(1, 2);


/**
 * A very simple PDO child , in order to make the use of PDO even
 * easier and prepare the statements only once.
 */
class Db extends PDO
{
    /**
     * The PDO statements that have been used.
     * Keys of the array are the text of the statement.
     * That way PDO statements instantiates only once.
     * @var array of PDOStatement
     */
    private $statements = array();

    /**
     * It takes a sql statement and replacement values for question marks placeholders (if there are any) executes the query by a prepared statement and
     * returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column.
     * @param $statement The query statement
     * @param none,string,array $values The replacement values as array for question marks placeholders if there are any. If there is only one then this can be a string
     * @throws Exception
     * @return null,array Returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column and the value.
     */
    public function request($statement,$values = null)
    {
        $result=array();
        $statement = trim($statement);

        if(is_null($values))
        {
            $values = array();
        }
        else if(!is_array($values))
        {
            $values = array($values);
        }

        if(substr_count($statement,"?") !== count($values))
        {
            throw new Exception(
            "PREPARED STATEMENT QUESTION MARKS MISSMATCH : ".$statement
            , 4561);
        }

        if(!isset($this->statements[$statement]))
        {
            $this->statements[$statement] = $this->prepare($statement);
        }

        $this->statements[$statement]->execute($values);
        if($this->statements[$statement]->errorCode()!="00000")
        {
            $error = $this->statements[$statement]->errorInfo();
            throw new Exception($error[2],(int)$error[0]);
        }

        $result = $this->statements[$statement]->fetchAll();
        return $result;
    }
}

/**
 * A very basic abstraction layer of data worker classes of the model
 */
abstract class Data_Worker
{
    /**
     * @var Db
     */
    protected $db;

    public function __construct(Db $db)
    {
        $this->db = $db;
    }
}


/**
 * The data worker for the folders
 */
class Model_Data_Folder extends Data_Worker
{
    private $insertSql = "INSERT INTO people_folders (parent_id,folder_name) VALUES (?,?)";
    private $selectAllSql = "SELECT * FROM people_folders ORDER BY id ASC";

    public function insert($parentId,$folderName)
    {
        $this->db->request($this->insertSql,array($parentId,$folderName));
        return $this->db->lastInsertId();
    }

    public function selectAll()
    {
        return $this->db->request($this->selectAllSql);
    }
}


/**
 * The data worker for the documents
 */
class Model_Data_Document extends Data_Worker
{
    private $insertSql = "INSERT INTO people_documents (folder_id,file_name) VALUES (?,?)";
    private $selectByFolderIdSql = "SELECT * FROM people_documents WHERE folder_id = ? ORDER BY id ASC";

    public function insert($folderId,$fileName)
    {
        $this->db->request($this->insertSql,array($folderId,$fileName));
    }

    public function selectByFolder($folderId)
    {
        return $this->db->request($this->selectByFolderIdSql,$folderId);
    }
}


class Model_Operations_CopyFolder
{
    /**
     * @var Model_Data_Folder
     */
    private $dataFolder; 

    /**
     * @var Model_Data_Document
     */
    private $dataDocument; 

    /**
     * Array that the first key is the id of the parent folder
     * the second key is the id of the child (nth childs)
     * (If we use it a lot should be application scope cached)
     * @var array
     */
    private $folderParents = array(); 

    /**
     * All original folder results array
     * (If we use it a lot should be application scope cached)
     */
    private $folderRows = array();

    public function __construct(Db $db)
    {
        $this->dataFolder = new Model_Data_Folder($db);
        $this->dataDocument = new Model_Data_Document($db);

        $results = $this->dataFolder->selectAll();
        foreach($results as $row)
        {
            $this->folderRows[$row["id"]] = $row;
            $this->folderParents[$row["parent_id"]][$row["id"]] = $row; 
        }
    }

    public function copyFolder($fromId,$toParentId)
    {
        $folderRow = $this->folderRows[$fromId];
        $newFolderId = $this->dataFolder
            ->insert($toParentId, $folderRow["folder_name"]);
        $documentRows = $this->dataDocument->selectByFolder($fromId);

        if(count($documentRows) > 0)
        {
            foreach($documentRows as $documentRow)
            {
                $this->dataDocument->insert($newFolderId, $documentRow["file_name"]);
            }
        }

        if(isset($this->folderParents[$fromId]))
        {
            foreach($this->folderParents[$fromId] as $id => $row)
            {
                $this->copyFolder($id, $newFolderId);
            }
        }
    }
}

?>

[jkon]

First of all your database structure could be quite different , maybe this is a test structure. This answer is based in the IndexedList model , but since the framework that contains its OOP way isn't published make a hybrid of it using arrays. You can use it when the list (or the array in this case) has reasonable amount of child objects. E.g. your folders table . If you use it a lot it has a meaning to start thinking about application scope caching , but this is another chapter. How it works: As you saw in the operation we store the result rows of the folders table in a array folderRows with its id as key and in an array folderParents that is two dimensional with the first dimention the parentId and the second dimension its own id. (in the IndexedList model the object is added into an array and its indexes associations in enother one , of course first you have to set indexes (for objects or for sublists). I used the Db object (in a very simple form) and Data Workers , as you can see I made it as simple as possible but if you understand how it works you can use it with any DB abstraction layer.

Sorry jkon - not ignoring great post - just answering klaus.

Let's say we add a new folder to folder id=4, which has lft=8 (ParentLEFT) and rgt=9 (ParentRIGHT).
The new fodler will have lft=9 (ParentRIGHT) and rgt=10 (ParentRIGHT+1)
All other folders where rgt >=ParentRIGHT will change rgt+2 (including parent folder).
Their lft will change to lft+2 IF their lft > ParentLEFT

This update can be done in one SQL query:

UPDATE people_folders SET lft = IF(lft <= ParentLEFT,lft,lft + 2), rgt = rgt + 2 WHERE rgt >= ParentRIGHT

You do the insert new folder afterwards or you could mess it up.

I did start scripting a Nested Sets Model, but seeing as jkon has produced this, and it seems very succinct, I'll leave it for now. However, if you want it, I'll pick it up again.

[jkon]

Hello Diafol, the solution I gave (making an implementation with arrays of the IndexedList Model as I described) is more generic (it wasn't designed for an once time job) and I would love to see if for this specific task the Nested Sets Model implementation would be more efficient (that would mean that there are some cases where the Nested Sets Model is more efficient in time , memory usage , db usage and CPU). If you create an example of it after all , remember that he don't want to update the tree but to take a branch down of folders (and their documents) and copy it to another branch (the original branch of folders that you copy and their documents is meant to stay intact)

Yes I realise the spec. - I've developed a class - nowhere near as succinct and swish as yours that copies the folders with a nested sets setup. I'll give you a taste, but it needs a lot of work:

    id  folder_name     lft     rgt  
------  -----------  ------  --------
     1  assets            1        18
     2  pages            19        26
     3  css               2         7
     4  js                8         9
     5  images           10        11
     6  media            12        17
     7  sections         20        23
     8  forms            24        25
     9  less              3         4
    10  sass              5         6
    11  video            13        14
    12  audio            15        16
    13  structure        21        22

Which translates as this:

Here's the class so far - I haven't done much with inserting new files yet. Still thinking about that.

 $pdo = new PDO("mysql:host=localhost;dbname=dwtest",'root','');

class diaFolder
{
    private $pdo;

    public function __construct($pdo)
    {
        $this->pdo = $pdo;
    }

    public function copy_folder($sourceId, $destinationParentId, $mode='copy')
    {
        $branchFolders = $this->get_branch_folders($sourceId);
        $branchFolderIds = $this->get_source_ids($branchFolders);

        $count = count($branchFolders);
        $incrementer = $count*2-1;

        list($parentLFT, $parentRGT) = $this->get_destination_folder($destinationParentId);

        $this->update_old_positions($parentLFT,$parentRGT,$incrementer);
        $this->insert_new_folders($branchFolders,$parentRGT,$count);
        $files = $this->get_files($branchFolderIds,$count);

        //Show only
        return ['list'=>$branchFolders,'count'=>$count,'inc'=>$incrementer, 'branchIds'=>$branchFolderIds,'parent'=>[$parentLFT,$parentRGT],'files'=>$files];
    }

    private function get_destination_folder($destinationParentId)
    {
        $sql = 'SELECT lft,rgt FROM people_folders WHERE id = :id';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':id',$destinationParentId,PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_NUM);
    }

    private function get_branch_folders($sourceId)
    {
        $sql = 'SELECT node.* FROM people_folders as node, people_folders as parent
                  WHERE node.lft BETWEEN parent.lft AND parent.rgt
                    AND parent.id = :id
                    ORDER BY node.lft';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':id',$sourceId,PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function get_source_ids($branchFolders)
    {
        return array_column($branchFolders, 'id');
    }

    private function update_old_positions($parentLFT,$parentRGT, $incrementer)
    {
        $sql = 'UPDATE people_folders SET lft = IF(lft <= ?,lft,lft + ?), rgt = rgt + ? WHERE rgt >= ?';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$parentLFT,$incrementer,$incrementer,$parentRGT]);
    }

    private function insert_new_folders($branchFolders, $parentRGT, $count)
    {
        $valueArray = [];
        $cnt = $parentRGT;
        foreach($branchFolders as $folder)
        {
            $diff = $folder['rgt'] - $folder['lft'];
            $valueArray = array_merge($valueArray, [$folder['folder_name'], $cnt, $cnt + $diff]);
            $cnt++;
        }
        $valueString = trim(str_repeat('(?,?,?),',$count),',');

        $sql = "INSERT INTO people_folders (`folder_name`,`lft`,`rgt`) VALUES $valueString";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($valueArray);
    }

    private function get_files($branchFolderIds, $count)
    {
        $folderString = trim(str_repeat('?,',$count),',');
        $sql = "SELECT `folder_id`,`file_name` FROM `people_documents` WHERE `folder_id` IN ($folderString)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($branchFolderIds);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }




}


$x = new diaFolder($pdo);
echo "<pre>";
print_r( $x->copy_folder(2,4) );
echo "</pre>";

The updating and inserting folders works well so far - I still need to provide or test a case for moving a branch to root level.
I've got the files info, but now need to insert with new folder ids.

Here is my people_documents:

   id  folder_id  file_name     
------  ---------  --------------
     1          2  about.php     
     2          2  contact.php   
     3          2  home.php      
     4          3  main.css      
     5          4  jquery.js     
     6          4  jqueryui.js   
     7          5  me.gif        
     8          5  map.jpg       
     9          5  logo.png      
    10          6  rugby.mp4     
    11          7  header.php    
    12          7  menu.php      
    13          8  login.php     
    14          8  register.php  
    15          9  serene.html   
    16          9  urbane.html 

There's no real connection between the type of file and the containing folder - Just random stuff.

[jkon]

Hello Diafol, I liked very match the name of the class ;). I wouldn't explain in depth now to why there is the false belief that the Nested Set Model is more efficient in most cases (one of this reasons has to do with the fact that there is a misunderstanding about what self referential hierarchical structure is with the use of it in the Adjacency List Model). Notice that if we where to make a query for every category to find its childs even the simple referential hierarchical structure without the IndexedList model could do it more efficiently. There are limitations in the IndexedList model but more generic use and limitations in the Adjacency List Model and many things that are very common in real life that it can't do without the use of excess memory usage from the language side and too many number of queries.

But lets move back to your example, If I got it correct before the diaFolder ;) class start we have:

CREATE TABLE IF NOT EXISTS `people_folders` (`id` int(10) NOT NULL AUTO_INCREMENT,`folder_name` varchar(100) NOT NULL,`lft` int(10) NOT NULL,`rgt` int(10) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
INSERT INTO `people_folders` (`id`, `folder_name`, `lft`, `rgt`) VALUES (1, 'assets', 1, 18),(2, 'pages', 19, 26),(3, 'css', 2, 7),(4, 'js', 8, 9),(5, 'images', 10, 11),(6, 'media', 12, 17),(7, 'sections', 20, 23),(8, 'forms', 24, 25),(9, 'less', 3, 4),(10, 'sass', 5, 6),(11, 'video', 13, 14),(12, 'audio', 15, 16),(13, 'structure', 21, 22);

The thing is that after running your example we have:

That isn't correct as you can see (for example we have two rgt 13 and so on)

[developer707]

Thank you guys for your support!
As my level of knowledge is not very good, it will take me some time to understand and test this examples.

@jkon - yes totally buggered the insert new folders. Actually it was simpler than I thought:

 private function insert_new_folders($branchFolders, $parentRGT, $count)
 {
    $valueArray = [];
    $diff = $branchFolders[0]['lft'] - $parentRGT;

    foreach($branchFolders as $folder)
    {
        $valueArray = array_merge($valueArray, [$folder['folder_name'], $folder['lft'] - $diff, $folder['rgt'] - $diff]);
    }

    $valueString = trim(str_repeat('(?,?,?),',$count),',');

    $sql = "INSERT INTO people_folders (`folder_name`,`lft`,`rgt`) VALUES $valueString";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($valueArray);
}

WRT efficiency - that's where I was coming from. The ALM is reported as being horribly inefficient the larger the number of levels / nested folders. I have to be honest, I'd never suggest the NSM for a simple menu system of maybe 3 levels, but as you know file structures can stoopidly deep. I was approaching this from the point of view of reducing the number of queries to a handful for any copy or move. From what I can see, ALM and ILM will run more queries as the folder structure deepens, or gets wider.

I have to admin I need to bone up on ILM, so possibly I missed the point wrt no. queries. Will continue to look at it. But I really like the minimal code you have in your class. Very smart :)

[jkon]

Hello Diafol , thank you for your comments and for taking the time to write this example (don't forget that still you had to copy the files (document_files) from the branch that you copy to the new folder accordingly). (No in that ILM implementations there will not be more queries as the tree deepens , but yes if there is an excessive number of folders will be slightly more memory usage) One scenario that NSM could work more efficiently is what you described , having a stoopidly deep tree e.g. system folders . But take a moment to think that even in this case NSM would work better (for a single folder and its associations in other tables and not a branch of folders) ONLY IF we search with the primary key (ID). I have never seen a real life scenario that it is that normally you look by the URL segments or in this case the folder name , you can't get the tree if you have lets say personal/documents vs business/documents both folders will have the same folder name (documents). Logically in a file system , each folder name is unique only in its parent folder so the only real option you have with NSM is to store in DB the full path , that means that every time you update or delete a folder you must take care on this in program side + DB usage and not only in DB side (that NSM claims to stand).

From the first time I have read and tested the Adjacency List Model (especially the implementation flourished in the PHP world) I realized that the all -level- thing is really a mess and it makes two assumptions that are not real , the first one is that you have to use only the DB and even so you can't use DB functions or stored procedures. So the ALM is just one implementation of the self referential hierarchical structure.

To solve those problems the Nested Set Model arose. It not only didn't provided an easier solution to the hierarchical structure but it leads to quite a great number of queries. Yes to get the branch down or up using an id is “easier” than ALM but this isn't normally the case in real life.

The Indexed List Model is nothing more than a usage programming side of the self referential hierarchical data structure , in that sense it doesn't require something different in the DB side. You could have almost the same usage with DB functions or with DB stored procedures if you prefer that way. I will write a tutorial for it (but first I should write a tutorial titled “A simple Data Layer for PHP Applications”, that after your tutorial about the MySQL in PHP will be easier because you made clearer the ground).

There are limitations to all the implementations. If the question were “I have 5.000.000 folders in a structure , what should I use ? ”. The answer would be , it depends on what you want to do with them. The only case NSM is better to that is only if you want to retrieve branch folders using an id or a unique key , and you don't care about other branches in your application. Normally I would go for use the simplest self referential hierarchical data structure and then implement it in your app in a need base, not always using the ILM in programming side.
Again thank you for this talk that I believe has demonstrated that some strong beliefs in the PHP world (as the power of the usage of NSM) are not based in facts.

Thanks jkon, it has certainly been an interesting discussion. I've managed to get the insert files to work too. I'll post my dirty class in a minute, but it does implement certain array functions, which as you know tend to be quite slow. I'm quite pleased with my first real foray into NSM as it showed that it wasn't as straightforward as the author of the link I provided (Hillier) suggested - either that or I spaghettified myself! ANyhow here's the completed first draft - which does seem to work for cases where a branch is not copied to the root.

class diaFolder
{
    /**
     * @var pdo
     */
    private $pdo;

    /**
     * @param $pdo PDO object
     */

    public function __construct($pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * @param $sourceId integer the first [parent] ID of the branch to be copied
     * @param $destinationParentId integer the ID of the folder to accept the new branch
     * @return array just a show of data - would be tidied up for real class
     */
    public function copy_folder($sourceId, $destinationParentId)
    {
        $branchFolders = $this->get_branch_folders($sourceId);
        $branchFolderIds = $this->get_source_ids($branchFolders);

        $count = count($branchFolders);
        $incrementer = $count*2-1;

        list($parentLFT, $parentRGT) = $this->get_destination_folder($destinationParentId);

        $this->update_old_positions($parentLFT,$parentRGT,$incrementer);
        $firstNewInsertId = $this->insert_new_folders($branchFolders,$parentRGT,$count);
        $this->insert_new_files($firstNewInsertId,$count, $branchFolderIds);

        //Show only
        return ['list'=>$branchFolders,'count'=>$count,'inc'=>$incrementer, 'branchIds'=>$branchFolderIds,'parent'=>[$parentLFT,$parentRGT]];
    }

    /**
     * @param $destinationParentId integer passed from copy_folder
     * @return mixed destination folder lft and rgt integers
     */
    private function get_destination_folder($destinationParentId)
    {
        $sql = 'SELECT lft,rgt FROM people_folders WHERE id = :id';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':id',$destinationParentId,PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_NUM);
    }

    /**
     * @param $sourceId integer passed from copy_folder
     * @return array branch folder records
     */
    private function get_branch_folders($sourceId)
    {
        $sql = 'SELECT node.* FROM people_folders as node, people_folders as parent
                  WHERE node.lft BETWEEN parent.lft AND parent.rgt
                    AND parent.id = :id
                    ORDER BY node.lft';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':id',$sourceId,PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * @param $branchFolders array passed from copy_folder
     * @return array array of IDs for folders being copied
     */
    private function get_source_ids($branchFolders)
    {
        return array_column($branchFolders, 'id');
    }

    /**
     * @param $parentLFT integer for first (parent) lft passed from copy_folder
     * @param $parentRGT integer for first (parent) rgt passed from copy_folder
     * @param $incrementer integer value to add to lft/rgt values on update 
     */
    private function update_old_positions($parentLFT,$parentRGT, $incrementer)
    {
        $sql = 'UPDATE people_folders SET lft = IF(lft <= ?,lft,lft + ?), rgt = rgt + ? WHERE rgt >= ?';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$parentLFT,$incrementer,$incrementer,$parentRGT]);
    }

    /**
     * @param $branchFolders array of records to be copied passed from copy_folder
     * @param $parentRGT integer for first (parent) rgt passed from copy_folder
     * @param $count integer total number of folders to be copied
     * @return integer the first ID added for new folders
     */
    private function insert_new_folders($branchFolders, $parentRGT, $count)
    {
        $valueArray = [];
        $diff = $branchFolders[0]['lft'] - $parentRGT;

        foreach($branchFolders as $folder)
        {
            $valueArray = array_merge($valueArray, [$folder['folder_name'], $folder['lft'] - $diff, $folder['rgt'] - $diff]);
        }

        $valueString = trim(str_repeat('(?,?,?),',$count),',');

        $sql = "INSERT INTO people_folders (`folder_name`,`lft`,`rgt`) VALUES $valueString";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($valueArray);
        return $this->pdo->lastInsertId();
    }

    /**
     * @param $branchFolderIds array IDs of folders to be copied passed from copy_folder
     * @param $count integer total number of folders to be copied 
     * @return array an array of files (records) to be copied
     */
    private function get_files($branchFolderIds, $count)
    {
        $folderString = trim(str_repeat('?,',$count),',');
        $sql = "SELECT `folder_id`,`file_name` FROM `people_documents` WHERE `folder_id` IN ($folderString)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($branchFolderIds);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * @param $firstInsertId integer first ID of copied folders
     * @param $count integer total number of folders to be copied
     * @param $branchFolderIds array IDs of folders to be copied passed from copy_folder
     */
    private function insert_new_files($firstInsertId, $count, $branchFolderIds)
    {
        $files = $this->get_files($branchFolderIds, $count);

        $insert = [];
        $newFolderIds = range($firstInsertId,$firstInsertId+$count-1);
        $keyArray = array_combine($branchFolderIds,$newFolderIds);
        foreach ($files as $file)
        {
            $insert = array_merge($insert, [$keyArray[$file['folder_id']],$file['file_name']]);
        }
        $folderString = trim(str_repeat('(?,?),',count($files)),',');
        $sql = "INSERT INTO `people_documents` (`folder_id`,`file_name`) VALUES $folderString";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($insert);
    }
}

$pdo = new PDO("mysql:host=localhost;dbname=dwtest",'root','');

$x = new diaFolder($pdo);
echo "<pre>";
print_r( $x->copy_folder(2,4) );
echo "</pre>";