xQzxnZ0f

1CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
2    `path_input` MEDIUMTEXT
3)
4RETURNS mediumtext CHARSET latin1
5LANGUAGE SQL
6NOT DETERMINISTIC
7CONTAINS SQL
8SQL SECURITY DEFINER
9COMMENT ''
10
11BEGIN
12
13CREATE TEMPORARY TABLE IF NOT EXISTS 
14t1
15ENGINE=MyISAM
16AS (
17    SELECT
18         n AS nr
19         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', tmp.n), '/', -1) AS catid
20         , (
21                SELECT name FROM category
22                WHERE category.id = catid
23            ) AS name
24         , (
25                SELECT path FROM category
26                WHERE category.id = catid
27            ) AS path
28    FROM
29      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
30            FROM category
31            CROSS JOIN (SELECT @rownum := 0) r
32        ) AS tmp 
33    GROUP BY catid
34    ORDER BY
35       n
36);
37
38INSERT INTO t2
39SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
40
41RETURN (SELECT path_long FROM t2 limit 1);
42
43END
44	
45CREATE TABLE `category` (
46    `id` INT(11) NOT NULL AUTO_INCREMENT,
47    `name` VARCHAR(100) NOT NULL,
48    `path` VARCHAR(100) NOT NULL DEFAULT '0',
49    PRIMARY KEY (`id`)
50)
51COLLATE='latin1_swedish_ci'
52ENGINE=InnoDB
53AUTO_INCREMENT=429
54;
55	
56INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
57INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
58INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
59INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
60INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
61INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
62INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
63INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
64INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
65INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
66INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
67INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');
68	
69SELECT decode_path(category.path) as decoded FROM category
70	
71decoded 
72A
73A
74A
75A
76A
77A
78A
79A
80A
81A
82A
83A
84	
85path
86/426/427/428
87/1/2/3
88/4/5/6
89/7/8/9
90	
91path_long
92/Computers/Other accessories/Laser printers
93/A/B/C
94/D/E/F
95/G/H/I