System of categories and subcategories / infinite children

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:

<?php 
function desceNaArvore($cat_pai = 0, $nivel = 0) {
    global $conn;
    $html = "";
    $query = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
    $query->execute(array($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);
    endwhile;
    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:)

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.

• Managing Hierarchical Data in MySQL

Solution 2: Closure table

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

• Moving Subtrees in Closure Table Hierarchies

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.