In and Around the Coding World

Innvo.com

Creating a Directory Tree with PHP & MySQL (Part 2)

Continuing from Part 1: Creating Trees with PHP & MySQL, if you ran the script on that page you will now have 2 MySQL tables.

The following script will provide a GUI for you to traverse the directory and make small alterations to it. More coming soon on this front! Run this script to see the results of the data created from the previous tutorial.

An added function for deleting and moving categories will be added soon.

<?php
 
// For calculating page load times
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
 
// Connect to MySQL
mysql_connect('localhost','root','root') or die('Cant connect to MySQL');
mysql_select_db('test') or die('Cant connect to MySQL');
 
$start = microtime();
$_tree = new tree;
 
echo '<h4>Acme Directory</h4>';
	// Lookup category and finish script if category is invalid
	$home = false;
		if(!isset($_GET['cat'])) 
			$home = true; // Home Page
		elseif(!$thiscat = $_tree->lookup($_GET['cat'])) // Category Lookup
			die("Invalid category\n");
 
		if(count($_POST)) // Add/Rename category
		{
			if(isset($_POST['renamecategory']))
			{
				if(($_POST['renamecategory'] = stripslashes($_POST['renamecategory'])) != $thiscat['name']) // Only update when name has changed
				{
				mysql_query('UPDATE category_tree SET name = \''.mysql_real_escape_string($_POST['renamecategory']).'\' WHERE lft = '.$thiscat['lft']) or die(mysql_error());
				mysql_query('CREATE TEMPORARY TABLE test.ttt (lft MEDIUMINT(8) unsigned NOT NULL ,hash BINARY( 16 ) NOT NULL);') or die(mysql_error());
				mysql_query('INSERT INTO ttt
					SELECT a1.lft,UNHEX(MD5(GROUP_CONCAT(a2.name ORDER BY a2.lft SEPARATOR \'/\')))
					FROM category_tree AS a1
					INNER JOIN category_tree AS a2 ON a2.lft > 0 AND a1.lft BETWEEN a2.lft AND a2.rgt
					WHERE a1.lft BETWEEN '.$thiscat['lft'].' AND '.$thiscat['rgt'].'
					GROUP BY a1.lft') or die(mysql_error());
				mysql_query('UPDATE ttt
					INNER JOIN category_tree ON ttt.lft = category_tree.lft
					SET category_tree.hash = ttt.hash;') or die(mysql_error());
				header('Location: http://'.$_SERVER['SERVER_NAME'].preg_replace("/{$thiscat['name']}/",$_POST['renamecategory'],$_SERVER['REQUEST_URI']));
				exit(0);
				}
			}
			if(isset($_POST['addsubcategory']) && ($_POST['addsubcategory'] = stripslashes($_POST['addsubcategory'])))
			{
				if($home) // Add a top level category
				{
				$query = mysql_query('SELECT category_tree.rgt,COALESCE(MAX(c2.id)+1,1) AS newid
					FROM category_tree
					INNER JOIN category_tree AS c2 ON 1
					WHERE category_tree.lft = 0');
				$row = mysql_fetch_array($query,MYSQL_ASSOC);
					if(!mysql_query('INSERT INTO category_tree (lft,rgt,depth,id,hash,name) VALUES ('.$row['rgt'].','.($row['rgt']+1).',1,'.$row['newid'].',UNHEX(MD5(\''.mysql_real_escape_string($_POST['addsubcategory']).'\')),\''.mysql_real_escape_string($_POST['addsubcategory']).'\')'))
						die('The category "'.$_POST['addsubcategory'].'" already exists in this tier');
				mysql_query('INSERT INTO category_top (id) VALUES ('.$row['newid'].')');
				mysql_query('UPDATE category_tree SET rgt = rgt + 2 WHERE lft = 0');
				header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
				exit(0);
				}
				else // Add category that is a top level category
				{
					if(mysql_num_rows(mysql_query('SELECT 1 FROM category_tree WHERE hash = UNHEX(MD5(\''.$_GET['cat'].'/'.$_POST['addsubcategory'].'\'))')))
						die('A subcategory with the name "'.$_POST['addsubcategory'].'" already exists in this tier');
				$query = mysql_query('SELECT COALESCE(MAX(id)+1,1) AS newid FROM category_tree');
				$row = mysql_fetch_array($query,MYSQL_ASSOC);
				mysql_query('UPDATE category_tree SET rgt = rgt + 2 WHERE rgt > '.$thiscat['lft']);
				mysql_query('UPDATE category_tree SET lft = lft + 2 WHERE lft > '.$thiscat['lft']);
				mysql_query('INSERT INTO category_tree (lft,rgt,depth,id,hash,name) VALUES ('.($thiscat['lft']+1).','.($thiscat['lft']+2).','.($thiscat['depth']+1).','.$row['newid'].',UNHEX(MD5(\''.mysql_real_escape_string($_GET['cat'].'/'.$_POST['addsubcategory']).'\')),\''.mysql_real_escape_string($_POST['addsubcategory']).'\')');
				header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
				exit(0);
				}
			}
		}
	// Parent and Subcategory Lookup when not the home page
		if(!$home)
			$_tree->p_and_c($thiscat['lft'],$thiscat['rgt'],$thiscat['depth']); 
 
 
	echo '<table border="1" width="90%" align="center"><tr><td colspan="3">';
		if($home)
			echo 'You\'ve struck the home page, select from the root categories below';
		else
		{
		echo '<h5>Breadcrumbs: <a href="tree.php">Top</a> &gt; ';
		// Generate breadcrumbs
		$base = array();
			foreach($_tree->parents as $inc => $parent)
			{
			$parent = explode("\t",$parent);
				if(!isset($_tree->parents[$inc+1]))
					echo $parent[0].' <sup>'.$parent[1].'</sup>';
				else
					echo '<a href="?cat='.urlencode(implode('/',$base).(count($base) ? '/' : '').$parent[0]).'">'.$parent[0].'</a>
					<sup>'.$parent[1].'</sup> &gt; ';
			$base[] = $parent[0];
			}
		echo '</h5>';
		}
 
	echo '</td></tr><tr valign="top"><td width="200">';
	echo '<h5>Subcategories</h5>
	<ul>';
	// Generate subcategories
		if($home)
		{
		$query = mysql_unbuffered_query('SELECT name FROM category_top AS ctop
			INNER JOIN category_tree AS ctree ON ctop.id = ctree.id
			ORDER BY name ASC') or die(mysql_error());
			while($row = mysql_fetch_array($query,MYSQL_ASSOC))
				echo '<a href="?cat='.urlencode($row['name']).'">'.$row['name'].'</a><br />';
		}
		else
		{
			foreach($_tree->children as $child)
			{
			$child = explode("\t",$child);
			echo '<li><a href="?cat='.urlencode($_GET['cat'].'/'.$child[0]).'">'.$child[0].'</a> <sup>'.$child[1].'</sup></li>';
			}
		}
	echo '</ul>';
 
	// Show options for editing
	echo '</td>';
	echo '<td>Your content goes here</td>
	<td width="200">'.($home ? '' : '
	<h5>Rename this Category</h5>
		<form method="post">
		<input type="text" name="renamecategory" value="'.htmlentities($thiscat['name']).'" />
		</form>').'
	<h5>Add Subcategory</h5>
		<form method="post">
		<input type="text" name="addsubcategory" />
		</form>
	</td>';
	echo '</td></tr>
	<tr><td colspan="3">Page generated in '.round(microtime()-$start,3).' seconds</td></tr></table>';
 
class tree
{
var $children = array();
var $parents = array();
	public function lookup($path) // Ensure Directory path is sound by checking MD5 of path in DB
	{
	$query = mysql_query('SELECT lft,rgt,depth,id,name
		FROM category_tree AS ctree
		WHERE ctree.hash = UNHEX(MD5(\''.mysql_real_escape_string($path).'\'))') or die(mysql_error());
		if(!$row = mysql_fetch_array($query,MYSQL_ASSOC))
			return false;
	return $row;
	}
 
	public function p_and_c($lft,$rgt,$depth) // Get parents and children
	{
	$query = mysql_unbuffered_query('SELECT GROUP_CONCAT(CONCAT_WS(\'\t\',children.name,ROUND((children.rgt - children.lft - 1) / 2)) SEPARATOR \'\n\') AS cols
		FROM category_tree AS children 
		WHERE children.lft BETWEEN '.($lft+1).' AND '.($rgt-1).' AND children.depth = '.($depth+1).'
		UNION ALL
		SELECT GROUP_CONCAT(CONCAT_WS(\'\t\',parents.name,ROUND((parents.rgt - parents.lft - 1) / 2)) SEPARATOR \'\n\') AS cols
		FROM category_tree AS parents 
		WHERE '.$lft.' BETWEEN parents.lft AND parents.rgt') or die(mysql_error());
	$this->children = preg_split("/\n/",array_shift(mysql_fetch_array($query,MYSQL_ASSOC)),-1,PREG_SPLIT_NO_EMPTY);
	$this->parents = array_slice(preg_split("/\n/",array_shift(mysql_fetch_array($query,MYSQL_ASSOC)),-1,PREG_SPLIT_NO_EMPTY),1);
	}
}
 
?>

Previous Article
Creating a Directory Tree with PHP & MySQL (Part 1)Next Article
Creating CSS Sprites with PHP

© Innvo.com | Privacy