Use Inner Join with <ul> and <li>

Question

Hello, I’m trying to list the Sub-Categories within their respective Categories with Inner Join, but I’m not getting it, see what’s happening with the Categories menu.

Below I relate the code used...

        <?php

    include "../conexao.php";
    $codigo = $_POST['codigo'];
    $nome_cat = $_POST['nome_cat'];
    $nome = $_POST['nome'];

    $query = mysql_query("SELECT * FROM categoria INNER JOIN sub_categoria ON categoria.nome_cat = sub_categoria.nome_cat")or die(mysql_error());
    while ($res = mysql_fetch_array($query)){
    ?>
            <ul>
                <li><a href="#"> <?php echo $nome_cat = $res['nome_cat'];?> </a>
                    <ul> 
                        <li><a href="prod_index_categoria.php?codcategoria=<?php echo $res['nome'];?>"><?php echo $res['nome'];?></a></li>
                    </ul>
                </li>
            <?php
    }
            ?>
            </ul>

Below I relate images of table structures...

Category:

Sub-category:

If friends can help me solve the problem by making Categories not duplicate, and Sub-Categories fall within their respective Categories, I would be most grateful.

Answer 1

I believe that the solution you are trying to find ends up complicating your project even more. Anyway it will duplicate unnecessary lines and bring irrelevant data to your query.

Try in the most practical and perhaps most agile way for your application which is a WHILE chained, sort of like this:

<ul>
<?php

    include "../conexao.php";
    $codigo = $_POST['codigo'];
    $nome_cat = $_POST['nome_cat'];
    $nome = $_POST['nome'];

    $query = mysql_query("SELECT codigo, nome_cat FROM categoria ORDER BY nome_cat") or die(mysql_error());

    while($res = mysql_fetch_array($query)) {

        echo '<li><a href="#">'.$res['nome_cat'].'</a>';

        $query2 = mysql_query("SELECT codigo, nome, nome_cat FROM sub_categoria WHERE nome_cat = '".$res['nome_cat']."' ORDER BY nome") or die(mysql_error());
        if(mysql_num_rows($query2) > 0) {
            echo '<ul>';
            while($res2 = mysql_fetch_array($query2)) {
                echo '<li><a href="prod_index_categoria.php?codcategoria='.$res2['codigo'].'">'.$res2['nome'].'</a></li>';
            }
            echo '</ul>';
        }

        echo '</li>';

    }
?>
</ul>

Considerations:

• Always try to list the fields you want in an sql query (as I used), thus saving database processing to find out which are all (*) to bring you.

• Use indexes in your tables and not names to reference another database table, besides space, are much faster, see that article.

I hope it helps, hugs

Answer 2

In my view your query is ok, the problem is in the relationship of the tables, since you used category name for such relation. Research and apply the concepts of Foreign key on its tables, which will improve both bank maintenance and performance.

----- Edit

I just realized, what you are asking in the query is not what you want to receive, the correct query would be

SELECT s.codigo, s.nome as sub_categoria, c.nome as categoria FROM sub_categoria s INNER JOIN categoria c ON s.cod_cat = c.cod_cat;