Get parent names of category > sub category >sub sub category upto 5 level

Question

table : categories

    -------------------------------------------
    | id | cat_name_en   | parent_id          |
    -------------------------------------------
    | 1  | level 1       |          0         |
    | 2  | level 2       |          1         |
    | 3  | Level 3       |          2         |
    | 4  | Level 4       |          3         |
    | 5  | level 5       |          4         |
    | 6  | test          |          1         |

here i need to get the name as

level 5 > level 4 > level 3 > level 2 > level 1

in my query i got names only upto 2

SELECT category,cat_id FROM ( SELECT CONCAT(p.cat_name_en, ' > ', c.cat_name_en) AS 'category',c.id as cat_id FROM categories c LEFT JOIN categories p ON c.parent_id = p.id ) s where cat_id = 5

Why isn't `test` not showing ? It is at the same level as `level 2` — Madhur Bhaiya, Nov 14 '18 at 10:02
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Nov 14 '18 at 10:15
By the way which MySQL version do you use? MySQL 8.0 has a better option then the current accepted answer. — Raymond Nijland, Nov 14 '18 at 10:23
"you can post answer for MySQL 8.0. People can learn new method too" Check the duplicated link in mine other comment @Samir it's all in there — Raymond Nijland, Nov 14 '18 at 10:31

score 0 · Answer 1 · answered Nov 14 '18 at 10:08

function CategoryTree(&$output=null, $parent=0, $indent=null){
    // conection to the database
    $db = new PDO("mysql:host=localhost;dbname=tutorial", 'root', '');
    // select the categories that have on the parent column the value from $parent
    $r = $db->prepare("SELECT id, name FROM categories WHERE parent=:parentid");
    $r->execute(array(
        'parentid'  => $parent
    ));
    // show the categories one by one
    while($c = $r->fetch(PDO::FETCH_ASSOC)){
        $output .= '<option value=' . $c['id'] . '>' . $indent . $c['name'] . "</option>";
        if($c['id'] != $parent){
            // in case the current category's id is different that $parent
            // we call our function again with new parameters
            CategoryTree($output, $c['id'], $indent . "&nbsp;&nbsp;");
        }
    }
    // return the list of categories
    return $output;
}

Samir Selia · Accepted Answer · 2018-11-14T10:27:55.580

0

Since you have maximum 5 levels of category, you can add LEFT JOIN for each level to form a hierarchy as needed,

SELECT t1.id as cat_id,
CONCAT_WS(' > ', t1.cat_name_en, t2.cat_name_en, t3.cat_name_en, t4.cat_name_en, t5.cat_name_en)
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.id = t1.parent_id
LEFT JOIN categories AS t3 ON t3.id = t2.parent_id 
LEFT JOIN categories AS t4 ON t4.id = t3.parent_id
LEFT JOIN categories AS t5 ON t5.id = t4.parent_id
WHERE t1.id = 5;

CONCAT_WS will not concat NULL values.

edited Nov 14 '18 at 10:27

answered Nov 14 '18 at 10:14

Samir Selia

7,007
2
11
30

@Rp9 what this answer does not really directly explain.. You need to add or remove LEFT JOIN's to match the sub sub levels needed.. – Raymond Nijland Nov 14 '18 at 10:21

Get parent names of category > sub category >sub sub category upto 5 level

2 Answers2