SQLize | PHPize | SQLtest

CREATE TABLE t_hierarchy ( id int(10) unsigned NOT NULL AUTO_INCREMENT, parent int(10) unsigned NOT NULL, PRIMARY KEY (id), KEY ix_hierarchy_parent (parent, id) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DELIMITER $$ CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT) BEGIN DECLARE _level INT; DECLARE _fill INT; INSERT INTO t_hierarchy (id, parent) VALUES (1, 0); SET _fill = 0; WHILE _fill < fill DO INSERT INTO t_hierarchy (parent) VALUES (1); SET _fill = _fill + 1; END WHILE; SET _fill = 1; SET _level = 0; WHILE _level < level DO INSERT INTO t_hierarchy (parent) SELECT hn.id FROM t_hierarchy ho, t_hierarchy hn WHERE ho.parent = 1 AND hn.id > _fill; SET _level = _level + 1; SET _fill = _fill + POWER(fill, _level); END WHILE; END $$ DELIMITER ; CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT NOT DETERMINISTIC READS SQL DATA 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(id) INTO @id FROM t_hierarchy WHERE parent = _parent AND 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 id, parent INTO _id, _parent FROM t_hierarchy WHERE id = _parent; END LOOP; END