-- 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.