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 ;