MySQL Category Tree

Inserting example data

If you want to try this out with some sample data, here's a PHP script to import the data from some formatted text. I won't explain in great detail, but the code and comments should be reasonably self explanatory.

<?php
// Connect to MySQL. Replace the username, password, and database
// (and possibly localhost) with your information
$sql = new mysqli("localhost", "username", "password", "exampledb");

// Our example categories; formatting should be self explanatory
$categories = <<<ENDLIST
Animals
Animals/Mammals
Animals/Mammals/Dogs
Animals/Mammals/Cats
Animals/Mammals/Horses
Animals/Reptiles
Animals/Reptiles/Snakes
Animals/Reptiles/Lizards
Materials
Materials/Feed
Materials/Feed/Fruit
Materials/Feed/Fruit/Fresh
Materials/Feed/Fruit/Dried
ENDLIST;

// Example products; {{product title}};{{category path}}
$products = <<<ENDLIST
Fluffy;Animals/Mammals/Cats
Mittens;Animals/Mammals/Cats
Spot;Animals/Mammals/Dogs
Rover;Animals/Mammals/Dogs
Duke;Animals/Mammals/Horses
MrSlithers;Animals/Reptiles/Snakes
Snappy;Animals/Reptiles/Lizards
Strange Iguana;Animals/Reptiles/Lizards
Apples;Materials/Feed/Fruit/Fresh
Bananas;Materials/Feed/Fruit/Fresh
Prunes;Materials/Feed/Fruit/Dried
Raisins;Materials/Feed/Fruit/Dried
Mixed feed;Materials/Feed
Flavourings and Ash;Materials/Feed
ENDLIST;

// Clear the tables of any extant data
$sql->query("TRUNCATE TABLE `categories`");
$sql->query("TRUNCATE TABLE `category_parent`");
$sql->query("TRUNCATE TABLE `products`");
$sql->query("TRUNCATE TABLE `product_parent`");

$id = 0;

// For storing our parsed category tree and associating category paths
// to inserted IDs for quick lookup when adding products
$categoryPaths = array();
$categoryData = array(
	'id'			=> 0,
	'node_path'		=> array(0=>0),
	'node_depth'	=> 0,
	'id_parent'		=> 0
);
$data = explode("\n", $categories);

// Transaction to improve speed
$sql->begin_transaction();

foreach($data as $v)
{
	// Skip empty lines
	$v = trim($v);
	if(!strlen($v)) { continue; }
	
	// Reference to current active node
	$node = &$categoryData;
	
	$path = explode("/", $v);
	$fullPath = array();
	
	// Looping through parts of each line
	foreach($path as $p)
	{
		if(!strlen($p)) { continue; }
		
		array_push($fullPath, $p);
		
		// If this category does not yet exist...
		if(!array_key_exists($p, $node))
		{
			// Build node data that we currently can;
			// $p is this part of the path (category title)
			$node[$p] = array(
				'node_depth'	=> $node['node_depth']+1,
				'node_path'		=> $node['node_path'],
				'id'			=> 0,
				'id_parent'		=> $node['id']
			);
			
			// Insert the category & get the ID
			$sql->query("INSERT INTO `categories` ".
				"(id_parent, node_path, node_depth, title) VALUES ".
				"({$node['id']}, '".implode(",", $node['node_path']).
				"', ".($node[$p]['node_depth']).", '{$p}')");
			
			$id = $sql->insert_id;
			
			// Attach the ID to the end of the node_path
			$sql->query("UPDATE `categories` SET node_path=CONCAT(node_path, ',', id) WHERE id={$id}");
			
			// Attach this category to all its ancestors
			$sql->query("INSERT INTO `category_parent` ".
				"(id_category, id_parent) VALUES ".
				"({$id}, {$node['id']})");
			$sql->query("INSERT INTO `category_parent` ".
				"(id_category, id_parent) ".
				"SELECT {$id}, id_parent FROM `category_parent` ".
				"WHERE id_category={$node['id']}");
			
			// Update the $node data and add the path to ID
			// reference
			$node[$p]['id'] = $id;
			array_push($node[$p]['node_path'], $id);
			$categoryPaths[implode("/", $fullPath)] = $id;
		}
		
		// Update the $node reference to this node
		$node = &$node[$p];
	}
}

$sql->commit();

// We just dump here for debugging - you don't have to.
var_dump($categoryData);

// Insert products
$data = explode("\n", $products);

$sql->begin_transaction();

foreach($data as $v)
{
	// Skip empty lines
	$v = trim($v);
	if(!strlen($v)) { continue; }
	
	// Parse product data.  If the category path doesn't exist, skip
	$prod = explode(";", $v);
	if(count($prod)!=2 || !array_key_exists($prod[1], $categoryPaths))
	{
		continue;
	}
	
	// Insert new product and get ID
	$sql->query("INSERT INTO `products` (title, id_parent) VALUES ".
		"('{$prod[0]}', ".$categoryPaths[$prod[1]].")");
	$id = $sql->insert_id;
	
	// Attach product to its parent & all parent ancestor categories
	$sql->query("INSERT INTO `product_parent` (id_product, id_parent) ".
		"VALUES ({$id}, ".$categoryPaths[$prod[1]].")");
	$sql->query("INSERT INTO `product_parent` ".
		"(id_product, id_parent) ".
		"SELECT {$id}, cp.id_parent FROM `category_parent` AS cp ".
			"WHERE cp.id_category=".$categoryPaths[$prod[1]]);
}

$sql->commit();
Page loaded in 0.012 second(s).