How to create a tree menu (multilevel) with PHP+ Mysql?

Question

I’m trying to make a tree menu with PHP+MySQL, but something is wrong with the code. And, the same table was used in ASP with SQLServer to make a menu:

First I press one array, my database:

$menu[] = Array('id'=>$row['NodeID'], 'texto'=>$row['NodeName'], 'idpai'=>$row['ParentID'], 'pasta'=>$row['IsFolder']);

And I get the function called after feeding the array:

function menuarvore($linhas,$idpai=1) { 
                                    $result = "<ul>"; 
                                        foreach ($linhas as $linha) {
                                            if ($linha['idpai'] == $idpai) { 
                                                    $result.= "<li>".$linha['texto']."</li>"; 
                                                        foreach ($linha as $r) { 
                                                                if ($r['idpai'] == $r['id']) $children = true; 
                                                                else $children = false; 
                                                        } 
                                                        if ($children = true) {
                                                                    $result.= menuarvore($linhas,$linha['id']) . "</li>";
                                                        }
                                            }
                                        } 
                                        $result .= "</ul>"; 
                                        return $result; 
                            }

But the result is not all elements in their due orders, it results only in that:

Empresa
Produto
    *Consultar
Importar
Extrato
   *Cadastrar
Avaliar
Relatórios
   *Regras
   *Acompanhamento
   *Apuração
Movimento
   *Consultar

The expected would be this:

And the database is like this:

CREATE TABLE IF NOT EXISTS `tax00_menu` (
  `NodeID` int(11) NOT NULL AUTO_INCREMENT,
  `idGrupo` int(11) DEFAULT NULL,
  `NodeName` varchar(100) DEFAULT NULL,
  `IsFolder` tinyint(4) DEFAULT NULL,
  `ParentID` varchar(10) DEFAULT NULL,
  `Link` varchar(100) DEFAULT NULL,
  `Comentarios` varchar(300) DEFAULT NULL,
  `Classificacao` varchar(100) DEFAULT NULL,
  `Cor` varchar(10) DEFAULT NULL,
  `Ativo` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`NodeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=44 ;

--
-- Extraindo dados da tabela `tax00_menu`
--

INSERT INTO `tax00_menu` (`NodeID`, `idGrupo`, `NodeName`, `IsFolder`, `ParentID`, `Link`, `Comentarios`, `Classificacao`, `Cor`, `Ativo`) VALUES
(1, 0, 'i9 TAX', 0, '-1', NULL, NULL, '0 ', NULL, 'S'),
(2, 0, 'Empresa ', 0, '1 ', NULL, NULL, '1 ', NULL, 'S'),
(3, 0, 'Consultar ', 1, '28', 'verCadastros_if.php', NULL, '1.1.1 ', NULL, 'S'),
(4, 0, 'Importar', 0, '1 ', NULL, NULL, '2 ', NULL, 'S'),
(5, 0, 'Ext. Calculadas ', 1, '4 ', 'are_if.php', NULL, '2.1 ', NULL, 'S'),
(6, 0, 'SPED', 1, '4 ', 'sped_if.php', NULL, '2.3 ', NULL, 'S'),
(7, 0, 'Avaliar ', 0, '1 ', NULL, NULL, '3 ', NULL, 'S'),
(8, 0, 'Calcular', 1, '7 ', 'confrontar_if.php', NULL, '3.1 ', NULL, 'S'),
(9, 0, 'Regras', 0, '2 ', NULL, NULL, '1.3 ', NULL, 'S'),
(10, 0, 'Consultar ', 1, '27', 'regraconsultar_if.php', NULL, '1.3.1.2 ', NULL, 'S'),
(11, 0, 'Cadastrar ', 1, '27', 'regracadastrar_if.php', NULL, '1.3.1.1 ', NULL, 'S'),
(12, 0, 'Grupos', 0, '2 ', NULL, NULL, '1.2 ', NULL, 'S'),
(13, 0, 'Cadastrar ', 1, '12', 'grupocadastrar_if.php', NULL, '1.2.1 ', NULL, 'S'),
(14, 0, 'Consultar ', 1, '12', 'grupoconsultar_if.php', NULL, '1.2.2 ', NULL, 'S'),
(15, 0, 'eDOC', 1, '4 ', 'edoc_if.php', NULL, '2.4 ', NULL, 'S'),
(16, 0, 'Associar', 1, '17', 'associarregra_if.php', NULL, '1.4.1 ', NULL, 'S'),
(17, 0, 'Associação', 0, '2 ', NULL, NULL, '1.5 ', NULL, 'S'),
(18, 0, 'Consultar ', 1, '17', 'associarconsultar_if.php', NULL, '1.4.2 ', NULL, 'S'),
(19, 0, 'Movimento ', 0, '1 ', NULL, NULL, '4 ', NULL, 'S'),
(20, 0, 'Consultar ', 1, '19', 'movimentoconsultar_if.php', NULL, '4.2 ', NULL, 'S'),
(21, 0, 'Excluir ', 1, '28', 'excluirMovimentacaoEmpresa_if.php', NULL, '1.1.2 ', NULL, 'S'),
(22, 0, 'Produto ', 0, '1 ', NULL, NULL, '5 ', NULL, 'S'),
(23, 0, 'Consultar ', 1, '22', 'produtoconsultar_if.php', NULL, '5.1 ', NULL, 'S'),
(24, 0, 'Exceção ', 0, '9 ', NULL, NULL, '1.3.1 ', NULL, 'S'),
(25, 0, 'Cadastrar ', 1, '24', 'cadastrarexcecao_if.php', NULL, '1.3.2.1 ', NULL, 'S'),
(26, 0, 'Consultar ', 1, '24', 'consultarexcecaoPrincipal_if.php', NULL, '1.3.2.2 ', NULL, 'S'),
(27, 0, 'Geral ', 0, '9 ', NULL, NULL, '1.3.2 ', NULL, 'S'),
(28, 0, 'Geral ', 0, '2 ', NULL, NULL, '1.1 ', NULL, 'S'),
(29, 0, 'XML ', 1, '4 ', 'xml_if.php', NULL, '2.5 ', NULL, 'S'),
(30, 0, 'Regras', 1, '31', 'relatorioassociacao_if.php', NULL, '6.2 ', NULL, 'S'),
(31, 0, 'Relatórios', 0, '1 ', NULL, NULL, '6 ', NULL, 'S'),
(32, 0, 'Acompanhamento', 1, '31', 'newrelatorioAcompanhamento_if.php', NULL, '6.1 ', NULL, 'S'),
(33, 0, 'Cadastrar ', 1, '40', 'cadastrarNotaExtrato_if.php', NULL, '4.1 ', NULL, 'S'),
(34, 0, 'Desbloquear ', 1, '28', 'desbloquearApuracao_if.php', NULL, '1.1.3 ', NULL, 'S'),
(35, 0, 'Ext. Não Calculadas ', 1, '4 ', 'are2_if.php', NULL, '2.2 ', NULL, 'S'),
(36, 0, 'Apuração', 1, '31', 'relatorioApuracao_if.php', NULL, '6.3 ', NULL, 'S'),
(37, 0, 'Exceção CFOP', 1, '28', 'excecaocfop_if.php', NULL, '1.1.4 ', NULL, 'S'),
(38, 0, 'Senha administrativa', 1, '28', 'senhaadministrativa_if.php', NULL, '1.1.5 ', NULL, 'S'),
(39, 0, 'Responsável Legal ', 1, '28', 'responsavellegal_if.php', NULL, '1.1.6 ', NULL, 'S'),
(40, 0, 'Extrato ', 0, '1 ', NULL, NULL, '21', NULL, 'S'),
(41, 0, 'Fontes Tributárias', 0, '2 ', NULL, '', '1.4 ', NULL, 'S'),
(42, 0, 'Cadastrar ', 1, '41', 'cadastroFonteTrib_if.php', NULL, '1.1 ', NULL, 'S'),
(43, 0, 'Consultar ', 1, '41', 'consultarFonteTrib_if.php', NULL, '1.2 ', NULL, 'S');

Answer 1

I was able to do it this way:

function display_children($parent, $level, $tabela) {
                                $sql = "SELECT 
                                                            a.NodeID, 
                                                            a.NodeName, 
                                                            a.Link, 
                                                            Deriv1.Count 
                                                            FROM  ".$tabela." a  
                                                            LEFT OUTER JOIN 
                                                                (SELECT 
                                                                    ParentID, 
                                                                    COUNT(*) AS Count 
                                                                FROM ".$tabela."   
                                                                GROUP BY ParentID) Deriv1 
                                                            ON a.NodeID = Deriv1.ParentID 
                                                            WHERE a.ParentID=".$parent."
                                                            ORDER BY a.Classificacao";


                                 $p_sql = Conexao::getInstance()->prepare($sql);
                                 $p_sql->execute();
                                 $resultado = $p_sql->fetchAll();

                                if ($level>1){ 
                                    $padding = $level*7;
                                    echo "<ul class='nav nav-third-level' style='padding-left: ".$padding."px;'>"; 
                                }else{
                                    echo "<ul class='nav nav-second-level' >";
                                }
                                foreach ($resultado as $row) {
                                    if ($row['Count'] > 0) {
                                        echo "<li><a href='" . $row['Link'] . "'><i class='fa fa-folder fa-fw'></i>" . $row['NodeName'] . "<span class='fa arrow'></span></a>";
                                        display_children($row['NodeID'], $level + 1, $tabela);
                                        echo "</li>";
                                    } elseif ($row['Count']==0) {
                                        echo "<li><a href='" . $row['Link'] . "'><i class='fa fa-link fa-fw'></i>" . $row['NodeName'] . "</a></li>";
                                    } else;
                                }
                                echo "</ul>";
                            }
                               ?>

And to call the function:

echo display_children(0, 1, $pojoModulo->getTabelaMenu());