System of infinite categories and subcategories/children

Question

I’m putting together a system of infinite categories and subcategories, simple to begin with. 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:

Listing in the way I adapted (considering incorrect) I have some limitations because when it comes to children of subcategories already children are not listed, I need a simple script that does such a procedure and is easy to customize.

Currently I have the following result:

My script for current listing:

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

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

    $selFilhos = $conn->prepare("SELECT * FROM cat WHERE cat_pai = ?");
    $selFilhos->execute(array($rowCategoria2->cat_id));
    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 = ?");
        $selSubFilhos->execute(array($rowFilho->cat_id));
        while($rowSubFilho = $selSubFilhos->fetch(PDO::FETCH_OBJ)):
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$rowSubFilho->cat_nome."<br/>";
        endwhile;

    endwhile;

} //fecha while categoria raiz

echo "</ul>"; ?>

Note: I know there are other ways to space the category besides "&nbsp" however, in this case it is only for testing.
The script I quoted earlier uses FETCH_ASSOC instead of FETCH_OBJ

Answer 1

In database this type of result you are searching for is with a type of query that is better known as Hierarchical query (Hierarchical query or recursive query) com adjacency list.

There is a lot of discussion about this because Mysql can’t stand it hierarchical queries like other Dbmss have like Postgresql and Oracle. You find a lot of crazy solutions out there creating extensive stored procedures, not very efficient.

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

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, in mysql it is the easiest way (read possible) to search for 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 workable solution that creates an additional table to store the hierarchy of records.

• Moving Subtrees in Closure Table Hierarchies

Answer 2

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 downside of this is that it will take multiple queries 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 points of improvement

• Use nested lists instead of indentation by spaces.

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

Answer 3

I thank @bfavaretto for the reply that also helped a lot and made me replay the project for being simpler, but I want to share here also the way I had previously solved to receive the answers.

In my case I ended up adapting 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 show 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 reply of @bfavaretto great for being simple and easy to adapt. Thank you!

Don’t 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.