SQL Hierarchal List with 1 query (breadcrumbs or categories) - MySQL

-- Add the following function to your MySQL database and replace the fields as needed to match your structure (hint: everything that starts with _ or @ don't touch): DROP FUNCTION IF EXISTS category_connect_by_parent_eq_prior_id; DELIMITER $$ CREATE FUNCTION category_connect_by_parent_eq_prior_id(value INT) RETURNS int(11) BEGIN DECLARE _id INT; DECLARE _parent INT; DECLARE _next INT; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL; SET _parent = @id; SET _id = -1; IF @id IS NULL THEN RETURN NULL; END IF; LOOP SELECT MIN(category_id) INTO @id FROM categories_table WHERE parent_id = _parent AND category_id > _id; IF @id IS NOT NULL OR _parent = @start_with THEN SET @level = @level + 1; RETURN @id; END IF; SET @level := @level - 1; SELECT category_id, parent_id INTO _id, _parent FROM categories_table WHERE category_id = _parent; END LOOP; END $$ DELIMITER ; -- Now use this query (again replace where needed the important parts) to give a structured hierarchal list of a category, all its sub-categories, and children therein recursively: SELECT `hi`.`category_id` AS `category_id`, `parent_id`, `level` FROM ( SELECT category_connect_by_parent_eq_prior_id(category_id) AS `id`, @level AS `level` FROM ( SELECT @start_with := 0, @id := @start_with, @level := 0 ) AS vars, `categories_table` WHERE @id IS NOT NULL ) AS `ho` JOIN `categories_table` AS `hi` ON `hi`.`category_id` = `ho`.`id`; -- Here's and example of what you'll see: category_id parent_id level 1 0 1 2 1 2 7 2 3 38 7 4

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.