PHP + MySQLi - no idea how to query DB to solve my problem

Question

I've got the following MySQLi-DB:

    ╔═════════════╦═══════════╦════════════════╗
    ║ category_id ║ parent_id ║ name           ║
    ╠═════════════╬═══════════╬════════════════╣
    ║ 28          ║ 1         ║ blog root      ║
    ╠═════════════╬═══════════╬════════════════╣
    ║ 30          ║ 28        ║ painting       ║
    ╠═════════════╬═══════════╬════════════════╣
    ║ 31          ║ 30        ║ kids painting  ║
    ╠═════════════╬═══════════╬════════════════╣
    ║ 32          ║ 30        ║ teens painting ║
    ╠═════════════╬═══════════╬════════════════╣
    ║ 35          ║ 28        ║ recipes        ��
    ╠═════════════╬═══════════╬════════════════╣
    ║ 36          ║ 28        ║ diy            ║
    ╚═════════════╩═══════════╩════════════════╝

It's category-tree

    blog root
        painting
            kids painting
            teens painting
        recipes
        diy

I need to create a query which will result all category_id's for a category with all it's subcategories.

EXAMPLE:

1) query with category_id = 30 --> result 30, 31, 32
2) query with category_id = 28 --> result 28, 30, 31, 32, 35, 36
3) query with category_id = 35 --> result 35

My newbe query is:

SELECT `category_id` FROM `blog_category` WHERE `category_id` = 28 OR `parent_id` = 28

this will result 28, 30, 35, 36 --> missing 31, 32

How can I solve that problem?

Edit: In MySQL not possible as it seems.

So how can I create with PHP and easy Mysql-queries an comma delimited string to do a final query like this solution on stackoverflow?

--->

... WHERE category_id IN ('$string')

Answer 1

You would typically use a recursive query to traverse the hierarchical tree. This feature is available in MySQL 8.0 only:

with recursive cte as (
    select category_id, parent_id, name, 1 lvl from mytable where category_id = 28
    union all
    select t.category_id, t.parent_id, t.name, c.lvl + 1
    from cte c
    inner join mytable t on t.parent_id = c.category_id
)
select * from cte order by lvl, category_id

This gives you one row per child, ordered by increasing depth, then by category_id.

If you want a comma separated value instead of a set of rows, then you can just change the last part of the query to use aggregation:

select group_concat(category_id order by lvl, category_id) all_category_ids
from cte