MySQL Category Tree

Inserting and Selecting data

Inserting new categories is pretty simple, and is achieved in 4 queries. Note the use of {{title}} and {{id_parent}} as placeholders for the new category title and the ID of the parent category (which can be 0, root).

INSERT INTO `categories` (title, id_parent, node_path, node_depth)
	(SELECT '{{title}}', {{id_parent}}, p.node_path, p.node_depth+1
		FROM `categories` AS p WHERE p.id={{id_parent}}) UNION
	(SELECT '{{title}}', {{id_parent}}, '0', 1) LIMIT 1;
UPDATE `categories` SET node_path=CONCAT(node_path, ',', id) WHERE id=LAST_INSERT_ID();
INSERT INTO `category_parent` (id_category, id_parent) VALUES
	(LAST_INSERT_ID(), {{id_parent}});
INSERT INTO `category_parent` (id_category, id_parent)
	SELECT LAST_INSERT_ID(), cp.id_parent FROM `category_parent` AS cp
		WHERE cp.id_category={{id_parent}};

These statements automatically create the node_path, calculate the node_depth, and create all of the category_parent attachments with very little required information!

Adding products

Adding products is even simpler, and requires only 3 queries. Obviously, in this example our products only have a title, but in your code would need any additional columns populated in the first query:

INSERT INTO `products` (id_parent, title) VALUES ({{id_parent}}, '{{title}}');
INSERT INTO `product_parent` (id_product, id_parent) VALUES (LAST_INSERT_ID(), {{id_parent}});
INSERT INTO `product_parent` (id_product, id_parent)
	SELECT LAST_INSERT_ID(), cp.id_parent FROM `category_parent` AS cp
		WHERE cp.id_category={{id_parent}};

Really, that's all that's needed for adding data.

Selecting information

Fetching a category from the categories table with its path (ancestor category titles) can now be done very simply. The code in bold shows how the path of parent categories is fetched (except {{id}}, which is the unique ID of the category we're looking up):

SELECT c.*, (SELECT
	GROUP_CONCAT(c2.title ORDER BY c2.node_depth ASC
	SEPARATOR '/') FROM `categories` AS c2 JOIN `category_parent` AS c2p
    ON c2.id=c2p.id_parent WHERE c2p.id_category=c.id) AS path FROM
    `categories` AS c WHERE c.id={{id}};

Furthermore, if we want to fetch the parent path as hyperlinks ready to be put straight into the page, we can do so. In this example, we'll wrap each ancestor category title in an anchor element that points to our imaginary page "category.php" which takes a GET name/value ?id={{category_id}}:

SELECT c.*, (SELECT
	GROUP_CONCAT(CONCAT('<a href="category.php?id=', c2.id, '">', c2.title, '</a>') ORDER BY c2.node_depth ASC
	SEPARATOR '/')
	FROM `categories` AS c2 JOIN `category_parent` AS c2p
    ON c2.id=c2p.id_parent WHERE c2p.id_category=c.id) AS path FROM
    `categories` AS c WHERE c.id={{id}};

If we want to select the ancestor categories of a specific category in more detail, we can also do so:

SELECT c2.id, c2.title FROM `categories` AS c1
	LEFT JOIN `category_parent` AS cp JOIN `categories` AS c2
	ON cp.id_parent=c2.id AND cp.id_category=c1
	WHERE c1.id={{id}}
	ORDER BY c2.node_depth ASC;

Fetching the Category Tree

If we want to fetch the whole category tree, we can do so easily:

SELECT * FROM `categories` ORDER BY node_path ASC;

This example, in PHP, fetches and indents depending on node depth all categories in nested unordered lists:

<?php
$sql = new mysqli("localhost", "username", "password", "database");

$res = $sql->query("SELECT * FROM `categories` ORDER BY node_path ASC");

$lastID = 0;
$nodeDepth = 1;
echo "<ul>\n";

while($row = $res->fetch_assoc())
{
	if($row['id_parent'] != $lastID)
	{
		echo "</li>\n";
	}
	
	if($row['node_depth'] > $nodeDepth)
	{
		echo /*"\n".str_repeat("\t", $nodeDepth).*/"<ul>\n";
		
		$nodeDepth++;
	}
	else if($row['node_depth'] < $nodeDepth)
	{
		do {
			echo "\n".str_repeat("\t", $nodeDepth-1)."</ul></li>\n";

			$nodeDepth--;
		} while($row['node_depth'] < $nodeDepth);
	}
	
	echo str_repeat("\t", $nodeDepth)."<li>{$row['title']}";
	$lastID = $row['id'];
}

if($lastID != 0) { echo "</li>\n"; }

do {
	echo str_repeat("\t", $nodeDepth-1)."</ul>";
	if($nodeDepth > 1) { echo "</li>"; }
	echo "\n";

	$nodeDepth--;
} while($nodeDepth >= 1);

$res->free();

Example data fetched like this:

<ul>
	<li>Animals<ul>
		<li>Mammals<ul>
			<li>Dogs</li>
			<li>Cats</li>
			<li>Horses</li>

		</ul></li>
		<li>Reptiles<ul>
			<li>Snakes</li>
			<li>Lizards</li>

		</ul></li>

	</ul></li>
	<li>Canned</li>
	<li>Materials<ul>
		<li>Feed<ul>
			<li>Fruit<ul>
				<li>Fresh</li>
				<li>Dried</li>
				<li>Canned</li>
			</ul></li>
		</ul></li>
	</ul></li>
</ul>
Page loaded in 0.026 second(s).