How to make a selection in MySQL with field filling relative to the hierarchy of the adjacent table?

Question

MySQL

Good afternoon, I have two simple tables category and product :
category:
product:

Categories have a simple nesting, which is reflected in the table:
Men's
-- Outerwear
---- Jackets
---- Coats
-- Accessories
---- Belts
- -----
Women's leather belts
-- Outerwear
---- Dresses
---- Skirts
-- Accessories
---- Bags
------Leather bags
How do I make a selection to get an extended product table as a result here is a view with an additional field tree , which would contain a hierarchy of categories from category? Or is there a way to do this in PHP? Ultimately, you need to get a CSV with fields like in the last
result image:

Answer 1

you can mess with concat and subquery , you can even do the mb procedure in sql, but usually this is already assembled at the level of the backend of the programming language