How to duplicate a tree-like nested structure in Database

What do I mean by a nested tree-like structure?

I simply mean that each node either has a parent_id as 0 which means it is a tree node. Or the node is a child of another node such in which parent id is the id of an existing node.

This is a basic pattern to keep trees. Something similar to this.

Problem.

While duplicating this tree-like structure is that when you duplicate you end up generating new ids for the whole tree. Which is not straight forward.

Idea.
My Algorithm is quite simple.

• Make a copy of all the existing nodes.
• Keep a mapping of node_ids and their new duplicate ID.
• On all the New Nodes inserted use the mapping to update the parent id to their new IDs.

The following is a sample code that I used to duplicate a similar structure.

<?php
include("connection.php");

$template_id = $_POST['template_id']; 
echo $check_templates_query  =   "SELECT *
                            FROM templates
                            where templates.id='".$template_id."'";
$templates_query_rs = mysqli_query($con,$check_templates_query) or die(mysqli_error($con));
$prow = mysqli_fetch_array($templates_query_rs);

/* Duplicating the Main Template. */
mysqli_query($con, "insert into templates(template_name, description, category_id) values('".$_POST["template_name"]."','".$_POST["description"]."','".$prow["category_id"]."')") or die(mysqli_error($con));
$new_template_id = mysqli_insert_id($con); 



/* Making copies of All the nodes and Saving Mapping. */

$id_mapping = array(); 
$new_folder_ids = array(); 

$template_folders   =   "SELECT template_folders.* from template_folders where 
                       template_id='".$template_id."'";
$template_folder_rs = mysqli_query($con,$template_folders) or die(mysqli_error($con));

while($row = mysqli_fetch_array($template_folder_rs))
{
	mysqli_query($con,"insert into template_folders(template_id, folder_name, pid,created_on, modified_on) values (
	'".$new_template_id."',
	'".$row["folder_name"]."',
	'".$row["pid"]."',
	'".date("Y-m-d H:i:s")."',
	'".date("Y-m-d H:i:s")."'	
	) ") or die(mysqli_error($con));
	$new_folder_id = mysqli_insert_id($con); 
	$folder_mapping[] = array(
								"id"=>$row["id"],
								"new_id"=>$new_folder_id 
							);
	$new_folder_ids[] = $new_folder_id; 
}

/* Updating the Parent ID's into New IDs through mapping. */

foreach($new_folder_ids as $new_id)
{
	$template_folders   =   "SELECT template_folders.* from template_folders where 
                        id='".$new_id."'";
	$template_folder_rs = mysqli_query($con,$template_folders) or die(mysqli_error($con));
	$row = mysqli_fetch_array($template_folder_rs); 
	if($row["pid"]!=0)
	{
		// Finding the mapping from the Array. 
		$key = array_search($row["pid"], array_column($folder_mapping, 'id'));
		$new_pid = $folder_mapping[$key]["new_id"]; 

		// Updating the column into the selected row. 
		mysqli_query($con,"update template_folders set pid='".$new_pid."' where id='".$row["id"]."'") or die(mysqli_error($con)); 
	}
}

The above is a sample quote of I achieve it.
It was a little challenging. I am definitely going to put this in the Machine test for the people I hire for programming.

If you need help in solving a similar issue you can contact me.