1

I would like to loop on each value of path column & split content by '/' delimiter & replace numbers with values from another column using MySQL & store result into another new column path_long.

Example

I have a column named path with content like '/426/427/428'. I would like to replace category id numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.

I have this partially working query:

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp 
    GROUP BY catid
    ORDER BY
       n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;

Result is:

path_long
/Computers/Other accessories/Laser printers

Here is the test DDL:

CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

Also test data:

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.

Problem

How to execute this query in a loop for each value of column named path and store the result to column named path_long?

Source column looks like (showing undecoded paths):

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

Desired result column should be like (showing decoded paths):

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

Basically it should decode a path having category ids to readable path format using category names. I showed examples how the result should look like. I added DDL to create test environment.

How can be better clarified? How can I help more? Now I added test case, why the question is still closed?

  • To clarify - you've got a query that can replace a hard-coded path's numeric values with the corresponding category values, placing the output in a temp table; you need to know how to modify that so the hard-coded value is, instead, the values from the path` column in the category table. Is this correct? – RDFozz Apr 23 '18 at 15:45
  • If I understand correctly your clarification, then yes. But this definition is more clear, I think: "should decode path with category ids to readable path format". – klor Apr 24 '18 at 4:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.