26LKBDVA

1CREATE DEFINER=`baza23115_dogfriendly`@`%.devil` PROCEDURE `explode_tags` (IN `bound` VARCHAR(255))  BEGIN
2
3    DECLARE tags TEXT;
4    DECLARE occurance INT DEFAULT 0;
5    DECLARE i INT DEFAULT 0;
6    DECLARE splitted_value TEXT;
7    DECLARE done INT DEFAULT 0;
8    DECLARE cur1 CURSOR FOR SELECT posts.tags
9                                         FROM posts
10                                         WHERE posts.tags != '';
11    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
12
13    DROP TABLE IF EXISTS tagstemp;
14    CREATE TABLE tagstemp(
15    `tags` VARCHAR(255) NOT NULL
16    ) ENGINE=Memory;
17
18    OPEN cur1;
19      read_loop: LOOP
20        FETCH cur1 INTO tags;
21        IF done THEN
22          LEAVE read_loop;
23        END IF;
24
25        SET occurance = (SELECT LENGTH(tags)
26                                 - LENGTH(REPLACE(tags, bound, ''))
27                                 +1);
28        SET i=1;
29        WHILE i <= occurance DO
30          SET splitted_value =
31          (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(tags, bound, i),
32          LENGTH(SUBSTRING_INDEX(tags, bound, i - 1)) + 1), ',', ''));
33
34          INSERT INTO tagstemp VALUES (TRIM(splitted_value));
35          SET i = i + 1;
36
37        END WHILE;
38      END LOOP;      
39    CLOSE cur1;
40    SELECT DISTINCT z.tags, (SELECT COUNT(*) FROM tagstemp as t WHERE t.tags = z.tags) as c FROM tagstemp as z ORDER BY c DESC;
41  END$$
42
43DELIMITER ;