php - how to create tree view in mysql using stored procedure

Question

I am confused. I don't know how to create a tree view in MySQL using stored procedure. I tried searching in Google and I don't understand how to query.

I have

deptid  |  dept_code |  dept_name  | parent_deptid
     1             1    wadir Umum           0
     2           101   bagian umum           1
     3         10101   kepala umum           2
     4           102   bagian privasi        1
     5       1010101   SUb bagian Tu         3
     6       1010102   bagian umum           3

and I want to make it like this

deptid  |  dept_code |  dept_name  |    parent_deptid
     1             1   wadir Umum                0
     2           101   -bagian umum              1
     3         10101   --kepala umum             2
     5       1010101   ---Sub bagian Tu          3 
     6       1010102   ---bagian umum            3
     4           102   -bagian privasi           1

Answer 1

Since department code pattern looks to be convenient, I didn't spent time on parent_id. According to your department code pattern, following should help.

Simply padding the dept code and getting the sortable uniform codes.

Change your tableName in query please.

SELECT * 
FROM tableName
ORDER BY RPAD(dept_code, 5, '0')

EDIT: Actually if parent_deptid was the actual parent's id then you would have just needed to sort by parent_deptid, then dept_code. However, parent_deptid doesn't look like the corresponding parent's id but something like "depth" instead.

EDIT2: Sorry, your parent_deptid looks to be ok, just needed to see more data showing other parent ids too. So I missed it. All you need to sort as following:

SELECT * 
FROM tableName
ORDER BY parent_deptid, dept_code;

EDIT3 - According to edited question: Back to my initial suggestion by changing the padded string length - Following is the most suitable solution for your data structure.

SELECT * 
FROM tableName
ORDER BY RPAD(dept_code, 10, '0')

• 10 could be the max length of your dept_code.