MySQL Category Tree

Adding to sample data

If you installed the sample data from the previous page, let's look at adding some data.

We'll insert a dog named "Bitey" to the "Dogs" category (we're assuming the ID of the Dogs category is 3):

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

We'll also add a sub category of the Food, Fruit; "Canned". This assumes the Fruit category has the ID 11:

INSERT INTO `categories` (title, id_parent, node_path, node_depth)
	(SELECT 'Canned', 11, p.node_path, p.node_depth+1
		FROM `categories` AS p WHERE p.id=11) UNION
	(SELECT 'Canned', 11, '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(), 11);
INSERT INTO `category_parent` (id_category, id_parent)
	SELECT LAST_INSERT_ID(), cp.id_parent FROM `category_parent` AS cp
		WHERE cp.id_category=11;

Finally, we'll add a new top-level (ID 0) category, "Dressage":

INSERT INTO `categories` (title, id_parent, node_path, node_depth)
	(SELECT 'Dressage', 0, p.node_path, p.node_depth+1
		FROM `categories` AS p WHERE p.id=0) UNION
	(SELECT 'Dressage', 0, '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(), 0);
INSERT INTO `category_parent` (id_category, id_parent)
	SELECT LAST_INSERT_ID(), cp.id_parent FROM `category_parent` AS cp
		WHERE cp.id_category=0;
Page loaded in 0.01 second(s).