MySQL get parent/child categories in order

Question

My question is pretty simple, but I don't know about the answer to it...

I have a table with the following fields and values:

Categories

ID | PARENT_ID | NAME
---------------------
 1 |     0     | Cat1
 2 |     0     | Cat2
 3 |     1     | Cat3
 4 |     1     | Cat4
 5 |     3     | Cat5
 6 |     2     | Cat6
 7 |     0     | Cat7
.....

Basically, I need to get the data using MySQL only - sorted by Parent_id. Also, for each Name, I need to add '-' sign depending on the level.
In the end it should look like this:

ID | PARENT_ID | NAME
---------------------
 1 |     0     | Cat1
 3 |     1     | -Cat3
 5 |     3     | --Cat5
 4 |     1     | -Cat4
 2 |     0     | Cat2
 6 |     2     | -Cat6
 7 |     0     | Cat7

I don't know how to start building such a query - I was thinking to order by parent_id, but it will not work.

Is there a function or a method that I could use to achieve this? Any hints will by much appreciated.

Answer 1

-- not really sure why, or what it could mean, but I think this will do what you are asking:

select ID,PARENT_ID,concat(substring('---------------',1,NAME),NAME) as NAME
from table
order by PARENT_ID

• note that if there are more than 15 'levels' (or PARENT_ID > 15), you'll need to add more '-'.

Answer 2

Try using REPEAT SQL function. By which you can do Select contact ( REPEAT( '-', parent_id), name) from table order by parent_id;