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

Question

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

Navigation

#1 by (0 votes)

1

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

First I load in a array , my database:

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

And I have 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 only results in this:

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

Expected this would be :

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

php mysql submenu

asked by anonymous 29.02.2016 / 21:07

1 answer

Window size scroll Reactive Programming With MEAN stack

score 0 · Accepted Answer

I was able to do the following:

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