Query in mysql with hierarchy

Question

Query in mysql with hierarchy

Navigation

#1 by (2 votes)

5

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 this table I have a department named Creation , still in this table I have another department named Schedule that is the child of the department Schedule has a child called Front End and so on, assuming my user is set as Creation how can I get all the levels below it including the children / em> using MySQL and PHP?

php mysql

asked by anonymous 08.07.2014 / 16:20

1 answer

Consultation in a relationship Many to Many in Laravel 4 Create Roles to Hide / Show Menu

score 2 · Accepted Answer

<?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);