System of categories and subcategories / infinite children


I'm building a system of infinite, simple categories and subcategories. I found a script on the internet that meets the requirements, but its customization and adaptation to my project made it almost unusable.

I studied a simple structure and adapted the database and the registration script correctly, but my biggest problem is being in the display part.

Based on the following records:



My script for the current listing:

echo "<ul>";
$selCategorias2 = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
while($rowCategoria2 = $selCategorias2->fetch(PDO::FETCH_OBJ)){

    echo $rowCategoria2->cat_nome."<BR/>";

    $selFilhos = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
    while($rowFilho = $selFilhos->fetch(PDO::FETCH_OBJ)):
        echo "&nbsp;&nbsp;&nbsp;&nbsp;".$rowFilho->cat_nome."<br/>";

        $selSubFilhos = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
        while($rowSubFilho = $selSubFilhos->fetch(PDO::FETCH_OBJ)):
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$rowSubFilho->cat_nome."<br/>";


} //fecha while categoria raiz

echo "</ul>"; ?>

Note: I know there are other ways to space the category other than "& nbsp" but in this case it's just for testing. Home   The script I mentioned earlier uses FETCH_ASSOC instead of FETCH_OBJ

asked by anonymous 14.04.2014 / 01:31

3 answers


With this database structure, you can use a recursive function (a function that calls itself) in PHP to extract as many levels as there are in the database from the tree. The disadvantage of this is that multiple queries will be required to the database.

Considering your current code, the shortest path would be a function like this:

function desceNaArvore($cat_pai = 0, $nivel = 0) {
    global $conn;
    $html = "";
    $query = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
    while($row = $query->fetch(PDO::FETCH_OBJ)):
        // Padding com espaços de acordo com o nível da categoria
        $html .= str_repeat("&nbsp;", $nivel * 4);
        // Nome da categoria
        $html .= $row->cat_nome . '<br>';
        // Filhos
        $html .= desceNaArvore($row->cat_id, $nivel + 1);
    return $html;

echo desceNaArvore();

Possible improvement points

  • Use nested lists instead of space-indented.

  • Separate the data acquisition logic from the display logic. Ideally, in terms of code organization, it would have a recursive function to get the data (in the form of nested arrays), and another, also recursive, to transform the array data into HTML. It is a suggestion of exercise if you want to study more about it:)

14.04.2014 / 03:38

In database this type of result you are looking for is a query type that is better known as Hierarchical query (Hierarchical Query or Recursive Query) with # adjacency list > .

There is a lot of discussion about this because MySQL does not support hierarchical queries like other DBMSs such as PostgreSQL and Oracle. You find various crazy solutions around creating extensive stored procedures, not very efficient.

The simplest way to fetch data with an adjacency list and with a number of levels is using #, it would be simpler and much faster than creating a loop inside the PHP loop running multiple queries, but even so, this model is far from ideal because it has some basic limitations.

It would look like this:

SELECT t1.cat_nome AS lev1, t2.cat_nome as lev2, t3.nome as lev3, t4.cat_nome as lev4
FROM cat AS t1
LEFT JOIN cat AS t2 ON t2.cat_pai = t1.cat_id
LEFT JOIN cat AS t3 ON t3.cat_pai = t2.cat_id
LEFT JOIN cat AS t4 ON t4.cat_pai = t3.cat_id

Solution: Nested Model

If you can change the structure of your database, I recommend you use this template, mysql is the easiest way (read if possible) to search data recursively.

There is a very detailed article that talks a lot about the model, the structure and how to implement it.

Solution 2: Closure table

Another feasible solution that creates an additional table to store the records hierarchy.

14.04.2014 / 02:42

Thanks to @bfavaretto for the answer that also helped a lot and made me redesign the project because it is simpler, but I want to share here also the way I had previously solved the answers.

In my case I adapted a little to display more data, you can adapt as you want, in this case just modify the function.

function imprimeMenuInfinito( array $menuTotal , $idPai = 0, $nivel = 0 )
        // abrimos a ul do menu principal
        echo str_repeat( "\t" , $nivel ),'<ul>',PHP_EOL;
        // itera o array de acordo com o idPai passado como parâmetro na função
        foreach( $menuTotal[$idPai] as $idMenu => $menuItem)
                // imprime o item do menu
                echo str_repeat( "\t" , $nivel + 1 ),'<li>',"ID: ", $menuItem['id']," - ", $menuItem['name'], ' <a href=?id=', $menuItem['id'], '>Excluir</a> <a href=?id_cat=', $menuItem['id'],'>Adicionar subcategoria</a> <a href=?edita_cat=', $menuItem['id'], '>Editar</a>' , PHP_EOL;
                // se o menu desta interação tiver submenus, chama novamente a função
                if( isset( $menuTotal[$idMenu] ) ) imprimeMenuInfinito( $menuTotal , $idMenu , $nivel + 2);
                // fecha o li do item do menu
                echo str_repeat( "\t" , $nivel + 1 ),'</li>',PHP_EOL;
        // fecha o ul do menu principal
        echo str_repeat( "\t" , $nivel ),'</ul>',PHP_EOL;

Here we display the function:

    $query = $conn->query('SELECT * FROM cat ORDER BY cat_nome ASC');
    while($row = $query->fetch(PDO::FETCH_OBJ)){
        $menuItens[$row->cat_pai][$row->cat_id] = array('name' => $row->cat_nome, 'pai' => $row->cat_pai, 'id' => $row->cat_id);

    echo imprimeMenuInfinito($menuItens);

I consider the response of @bfavaretto to be simple and easy to adapt. Thanks!


Do not forget the connection file
  When adding new attributes from the database you should send them via array to the function, then call them in function.

15.04.2014 / 21:53