Query in mysql with hierarchy

Question

I have a table called 'departments' with the following structure:

CREATE TABLE `departments` (
  `id` tinyint(255) NOT NULL AUTO_INCREMENT,
  `company_id` tinyint(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=23;

In that table I have a department called Creating, still in that table I have another department called Programming who is the son of department Creating, the department Programming has a son named Front-End and so it goes, assuming that my user is set as Creating how can I catch all the levels below it including the children of children using Mysql and PHP?

Answer 1

<?php

    //id referente ao cadastro de usuário.
    $depto_id  = 1;

    //conexão com banco de dados
    $conn      = mysqli_connect("localhost", "root", "senha", "testdb");

    $sql   = 'SELECT a.id, a.name, b.id as parent_id, b.name as parent_name';
    $sql  .= ' FROM departments a';
    $sql  .= ' LEFT JOIN departments b on b.parent_id=a.id';
    $sql  .= ' WHERE a.id=?';
    $sql  .= ' ORDER BY a.id, b.id';    

    function getNiveis($conn, $sql, $id, &$result = array())
    {
        $stmt = mysqli_prepare($conn, $sql);    
                mysqli_stmt_bind_param($stmt, 'i', $id);
                mysqli_execute($stmt);
                mysqli_stmt_bind_result($stmt, $id, $name, $parent_id, $parent_name);
                mysqli_stmt_fetch($stmt);
        array_push($result, array('id' => $id, 
                                  'name' => $name, 
                                  'parent_id' => $parent_id, 
                                  'parent_name' => $parent_name));              
        mysqli_stmt_close($stmt);
        if (!is_null($parent_id) && is_numeric($parent_id))
        {
            getNiveis($conn, $sql, $parent_id, $result);
        }       

    }

    $result = array();
    getNiveis($conn, $sql, $depto_id, $result);

    $nivel = NULL;
    foreach($result as $value){
        if (empty($nivel) === false) 
        {
            $nivel .= ' => ' ;
        }
        $nivel .= $value['name'];
        //tem todos esses valores em $value
        //$value['id']
        //$value['name']
        //$value['parent_id']
        //$value['parent_name']
    }
    echo $nivel;
    mysqli_close($conn);