I am currently developing an inventory system for my company.  I wanted to create a category listing but I have always struggled with creating hierarchy and storing it in a database.  I created a CMS package at my last place of employment. I remember spending a great deal of time working out how to create hierarchy and then adding to and updating the hierarchy.

I decided to do some research on the topic and came across the Sitepoint.com article titled “Storing Hierarchical Data in a Database.” The author discussed two models: Adjacency List & Modified Preorder Tree Traversal. The second model stood out as the option I wanted.  As he pointed out it takes a bit of time to understand the workings but once I did it was the right fit for the job.

So I began creating my hierarchy as well as the code to add nodes to the tree.  However, as I continued working on the inventory system I knew that I would need a method to reorganize the hierarchy.  Unfortunately, the author did not provide any code on update node order.

So I began working on the method and this is what I came up with.

<?php
	function update()
	{
		/*** Get left and right bounds for the category item to be moved ***/

		$query = sprintf("SELECT Parent, CategoryId, Category, lft, rgt FROM j_inventory_category WHERE CategoryId = '%d'",
								mysql_real_escape_string($_POST['CategoryId']) );

		$result = mysql_query($query);

		while ($row = mysql_fetch_object($result))
		{
			$cat_lft = $row->lft;
			$cat_rgt = $row->rgt;
		}

		$space = ($cat_rgt-$cat_lft) + 1;

		/*** Get all items under the selected category ***/

		$result = mysql_query("SELECT CategoryId, Category, lft, rgt FROM j_inventory_category
								WHERE lft BETWEEN $cat_lft AND $cat_rgt ORDER BY lft ASC");  

		$arr_category = Array();
		while ($row = mysql_fetch_object($result))
		{
				$arr2 = Array();
				$arr2['CategoryId'] = $row->CategoryId;
				$arr2['lft'] = $row->lft;
				$arr2['rgt'] = $row->rgt;
				array_push($arr_category, $arr2);
		}

		/*** Move all upper categories to fill in the space left by moving the item ***/

		$query = "UPDATE j_inventory_category SET rgt=rgt-$space WHERE rgt>$cat_rgt";
		mysql_query($query);

		$query = "UPDATE j_inventory_category SET lft=lft-$space WHERE lft>$cat_lft";
		mysql_query($query);

		/**** Reset lft & rgt for all items under the new chosen Parent ****/

		$query = sprintf("SELECT Parent, CategoryId, Category, lft, rgt FROM j_inventory_category WHERE CategoryId = '%d'",
								mysql_real_escape_string($_POST['Parent']) );

		$result = mysql_query($query);

		while ($row = mysql_fetch_object($result))
		{
			$parent_lft = $row->lft;
			$parent_rgt = $row->rgt;
			$lft = $row->rgt;
		}

		$query = "UPDATE j_inventory_category SET rgt=rgt+$space WHERE rgt>=$parent_rgt";
		mysql_query($query);

		$query = "UPDATE j_inventory_category SET lft=lft+$space WHERE lft>$parent_rgt";
		mysql_query($query);

		/*** Update chosen Category item and any children categories with new right and left bounds ****/

		for($x=0;$x<count($arr_category);$x++) {
				$rgt = $lft + ($arr_category[$x]['rgt'] - $arr_category[$x]['lft']);

				if($_POST['CategoryId'] == $arr_category[$x]['CategoryId'])
				{
						$query = sprintf("UPDATE j_inventory_category
										SET lft=$lft, rgt=$rgt, Parent = %d, Category ='%s'
										WHERE CategoryId = %d",
										mysql_real_escape_string($_POST['Parent']),
										mysql_real_escape_string($_POST['Category']),
										mysql_real_escape_string($_POST['CategoryId']) );
				}
				else
				{
						$query = sprintf("UPDATE j_inventory_category
										SET lft=$lft, rgt=$rgt
										WHERE CategoryId = %d",
										mysql_real_escape_string($arr_category[$x]['CategoryId']) );
				}
				$lft++;
				mysql_query($query);
		}
	}
?>

Originally, I wrote it using the basic functionality of the addition code given by the author but quickly realized that would not work if I wanted to move a category node with children nodes. Hopefully this will help someone else implementing similar functionality. Also if anyone comes up with ways to cut down on the number of queries I would appreciate the feedback.