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.

Answer 2

Chnage your dept_code column type to VARCHAR and use next query

SELECT * 
FROM tableName
ORDER BY dept_code

Answer 3

The following query will create a markup output:

SELECT group_concat(
  CONCAT(
    REPEAT('    ', (CHAR_LENGTH(t.dept_code) - 1) / 2),
    '- ',
    t.dept_name
  )
  ORDER BY t.dept_code
  SEPARATOR '\n'
) AS markup
FROM Table1 t

sqlfiddle

Result:

- wadir Umum
    - bagian umum
        - kepala umum
            - SUb bagian Tu
        - bagian umum
    - bagian privasi

Will be rendered to:

• wadir Umum
  • bagian umum
    • kepala umum
      • SUb bagian Tu
    • bagian umum
  • bagian privasi

Update

To match the question update:

SELECT t.*,
  CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', '')) AS indent,
  CONCAT(
    REPEAT('-', CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', ''))),
    t.dept_name
  ) AS indented_name
FROM Table1 t
ORDER BY t.dept_code

sqlfiddle

Update 2

The benefit of your design is that you do not need a stored procedure for such tasks. See the dept_code as the full tree path with 0 as separator. 1010102 could also be written as 1/1/1/2. If designed correctly you can just order by dept_code. To get node depth you just need to count the separator (0) in the path (dept_code).

Update 3

If you want to create a recursive structure, you better do in a procedural language like PHP:

Store SQL result from a simple query (SELECT * FROM depts) into an array, which would look like:

// result from query: SELECT * FROM depts
$depts = array(
    array( // row #0
        'deptid' => 1,
        'dept_code' => '1',
        'dept_name' => 'wadir Umum',
        'parent_deptid' => 0,
    ),
    array( // row #1
        'deptid' => 2,
        'dept_code' => '101',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 1,
    ),
    array( // row #2
        'deptid' => 3,
        'dept_code' => '10101',
        'dept_name' => 'kepala umum',
        'parent_deptid' => 2,
    ),
    array( // row #3
        'deptid' => 4,
        'dept_code' => '102',
        'dept_name' => 'bagian privasi',
        'parent_deptid' => 1,
    ),
    array( // row #4
        'deptid' => 5,
        'dept_code' => '1010101',
        'dept_name' => 'SUb bagian Tu',
        'parent_deptid' => 3,
    ),
    array( // row #5
        'deptid' => 6,
        'dept_code' => '1010102',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 3,
    ),
);

Build a recursive structure with two foreach loops:

$nodes = array();
$roots = array();

// init nodes
foreach ($depts as $dept) {
    $dept['childs'] = array(); // init childs
    $nodes[$dept['deptid']] = $dept;
}

foreach ($depts as $dept) {
    if ($dept['parent_deptid'] == 0) {
        $roots[] = $dept['deptid']; // add root
    } else {
        $nodes[$dept['parent_deptid']]['childs'][] = $dept['deptid']; // add to parents chlids list
    }
}

The arrays $roots and $nodes will look like:

$roots = array (0 => 1,);
$nodes = array(
    1 => array(
        'deptid' => 1,
        'dept_code' => '1',
        'dept_name' => 'wadir Umum',
        'parent_deptid' => 0,
        'childs' => array(
            0 => 2,
            1 => 4,
        ) ,
    ) ,
    2 => array(
        'deptid' => 2,
        'dept_code' => '101',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 1,
        'childs' => array(
            0 => 3,
        ) ,
    ) ,
    3 => array(
        'deptid' => 3,
        'dept_code' => '10101',
        'dept_name' => 'kepala umum',
        'parent_deptid' => 2,
        'childs' => array(
            0 => 5,
            1 => 6,
        ) ,
    ) ,
    4 => array(
        'deptid' => 4,
        'dept_code' => '102',
        'dept_name' => 'bagian privasi',
        'parent_deptid' => 1,
        'childs' => array() ,
    ) ,
    5 => array(
        'deptid' => 5,
        'dept_code' => '1010101',
        'dept_name' => 'SUb bagian Tu',
        'parent_deptid' => 3,
        'childs' => array() ,
    ) ,
    6 => array(
        'deptid' => 6,
        'dept_code' => '1010102',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 3,
        'childs' => array() ,
    ) ,
)

Demo

Now you can write some recursive function to walk through the tree:

function getSubtreeHTMLList($deptsids, $nodes) {
    $result = '<ul>';
    foreach ($deptsids as $deptsid) {
        $result .= '<li>';
        $result .= $nodes[$deptsid]['dept_name'];
        if (count($nodes[$deptsid]['childs'] > 0)) {
            $result .= getSubtreeHTMLList($nodes[$deptsid]['childs'], $nodes);
        }
        $result .= '</li>';
    }
    $result .= '</ul>';
    return $result;
}

echo getSubtreeHTMLList($roots, $nodes);

Created HTML:

<ul><li>wadir Umum<ul><li>bagian umum<ul><li>kepala umum<ul><li>SUb bagian Tu<ul></ul></li><li>bagian umum<ul></ul></li></ul></li></ul></li><li>bagian privasi<ul></ul></li></ul></li></ul>

Demo

Rendered:

• wadir Umum
  • bagian umum
    • kepala umum
      • SUb bagian Tu
      • bagian umum
  • bagian privasi