MuznxYBq

1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS get_inv_cat_ancestory $$
4
5CREATE PROCEDURE get_inv_cat_ancestory( IN selected_cat_id int )
6BEGIN
7
8 DECLARE parent_id int;
9 DECLARE child_id int;
10 SET child_id = selected_cat_id;
11 SET parent_id = 0;
12
13 -- Create temp table
14 CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS 
15    ( SELECT * FROM inventory_categories WHERE 1 = 0 );
16 TRUNCATE TABLE temp_table;
17
18 -- Add the selected category to the results
19 INSERT INTO temp_table 
20    SELECT * FROM inventory_categories 
21    WHERE id = selected_cat_id;
22
23 -- Get the parent of selected_cat_id
24 SELECT parent INTO parent_id 
25 FROM temp_table WHERE id = selected_cat_id;
26
27 WHILE parent_id != 0 DO
28
29   -- Add this category to the temp table
30   INSERT INTO temp_table 
31    SELECT * FROM inventory_categories 
32    WHERE id = parent_id;
33
34   SET child_id = parent_id;
35   SET parent_id = 0;
36
37   -- This will be the next ancestor in the loop
38   SELECT parent into parent_id
39   FROM inventory_categories WHERE id = child_id;
40
41 END WHILE;
42
43 -- Return the complete ancestory
44 SELECT * FROM temp_table;
45
46END $$
47
48DELIMITER ;