Menu, submenus Dynamic NAV Bootstrap with PHP PDO Mysql

Question

Good night.

I need to assemble a dynamic menu, of three tables, that need to be interconnected, but I’m lost. Follow a part of the organization chart below:

At the moment, I have the following code:

<li class="dropdown">
    <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Produtos<span class="caret"></span></a>
    <ul class="dropdown-menu">

    <li class="dropdown dropdown-submenu"><a class="dropdown-toggle" href="#" data-toggle="dropdown">Audiologia</a>
    <ul class="dropdown-menu">

      <?php

      require 'conexao.php';

      $consulta = $PDO->query("SELECT * FROM subcategoria WHERE categoria_id = '1' ORDER BY ID;");

      while ($linha = $consulta->fetch(PDO::FETCH_ASSOC)) { ?>

      <li><a href="produto.php?id=<?php echo "$linha[id]"; ?>"><?php echo "$linha[titulo]"; ?></a></li>

      <?php } ?>

      <ul class="dropdown-menu">

        <?php

        require 'conexao.php';

        $consulta = $PDO->query("SELECT * FROM produto WHERE categoria_id = '1' AND subcategoria_id = '1' ORDER BY ID;");

        while ($linha = $consulta->fetch(PDO::FETCH_ASSOC)) { ?>

        <li><a href="produto.php?id=<?php echo "$linha[id]"; ?>"><?php echo "$linha[titulo]"; ?></a></li>

        <?php } ?>

      </ul>

    </ul>
</li>

In my database, I have the tables:

CATEGORIES = with main categories, ID and CATEGORY.

SUBCATEGORIES = with ID, CATEGORIA_ID and SUBCATEGORY fields.

PRODUCT = with ID, CATEGORIA_ID, SUBCATEGORIA_ID and TITLE fields.

I can, from this code, have the category and the sub category, but not the product. I don’t think this is the right way to do it, I’d like instructions.

Answer 1

Try this:

<?php require_once('conexao.php');
$id_categoria = 1;
$consulta = $PDO->prepare("SELECT * FROM subcategoria WHERE categoria_id = :idCategoria ORDER BY ID;");
$consulta->bindParam(':idCategoria', $id_categoria, PDO::PARAM_INT);
$consulta->execute();
while ($linha = $consulta->fetch(PDO::FETCH_ASSOC)) {
    $categoria[$linha['id']]['titulo'] = $linha['titulo'];
    $subConsulta = $PDO->prepare("SELECT * FROM produto WHERE categoria_id = :idCategoria AND subcategoria_id = :idSubcategoria ORDER BY ID;");
    $subConsulta->bindParam(':idCategoria', $id_categoria, PDO::PARAM_INT);
    $subConsulta->bindParam(':idSubcategoria', $linha['id'], PDO::PARAM_INT);
    $subConsulta->execute();
    while ($sublinha = $subConsulta->fetch(PDO::FETCH_ASSOC)) {
        $categoria[$linha['id']]['subitens'] = array($sublinha['id'], $sublinha['titulo']);
    }
}
?>

<li class="dropdown">
    <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Produtos<span class="caret"></span></a>
    <ul class="dropdown-menu">
        <li class="dropdown dropdown-submenu"><a class="dropdown-toggle" href="#" data-toggle="dropdown">Audiologia</a>
            <ul class="dropdown-menu">
                <?php foreach ($categoria as $indice => $item) { ?>
                    <li><a href="produto.php?id=<?php echo $indice; ?>"><?php echo $item['titulo']; ?></a></li>
                    <ul class="dropdown-menu">
                        <?php foreach ($item['subitens'] as $subIndice => $subitem) { ?>
                            <li><a href="produto.php?id=<?php echo $subIndice; ?>"><?php echo $subitem; ?></a></li>
                        <?php } ?>
                    </ul>
                <?php } ?>
            </ul>
        </li>
    </ul>
</li>

Answer 2

So the code gets smaller and easier to see, as much as my code isn’t 100% the way you need it, to adjust it is simpler this way.

In the second query I put it to get the id of the first query and in case you need to use another foreach for the categories I left separate the $id_category variable to be simpler to replace.

<?php
require 'conexao.php';
$id_categoria = 1;
$consulta = $PDO->query("SELECT * FROM subcategoria WHERE categoria_id = '".$id_categoria."' ORDER BY ID;");
while ($linha = $consulta->fetch(PDO::FETCH_ASSOC)) {
    $categoria[$linha['id']]['titulo'] = $linha['titulo'];
    $subconsulta = $PDO->query("SELECT * FROM produto WHERE categoria_id = '".$id_categoria."' AND subcategoria_id = '" . $linha['id'] . "' ORDER BY ID;");
    while ($sublinha = $subconsulta->fetch(PDO::FETCH_ASSOC)) {
        $categoria[$linha['id']]['subitens'] = array($sublinha['id'], $sublinha['titulo']);
    }
}
?>

<li class="dropdown">
    <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Produtos<span class="caret"></span></a>
    <ul class="dropdown-menu">
    <li class="dropdown dropdown-submenu"><a class="dropdown-toggle" href="#" data-toggle="dropdown">Audiologia</a>
    <ul class="dropdown-menu">
        <?php foreach($categoria as $indice => $item): ?>
        <li><a href="produto.php?id=<?php echo $indice; ?>"><?php echo $item['titulo']; ?></a></li>
        <ul class="dropdown-menu">
            <?php foreach($item['subitens'] as $subindice => $subitem): ?>
            <li><a href="produto.php?id=<?php echo $subindice; ?>"><?php echo $subitem; ?></a></li>
            <?php endforeach; ?>
        </ul>
        <?php endforeach; ?>
    </ul>
</li>