Subcategories within their respective Category in the menu

Question

Hi, I created a menu that searches the information in the database. I have a table with the categories and another one with the subcategories, up to there beauty, my difficulty is to make each subcategory is listed within their respective category (example below)...

But I’m not succeeding, it is as an example below...

Below I am attaching the code used for the menu...

    <?php
    include "../conexao.php";
    $codigo = $_POST['codigo'];
    $nome = $_POST['nome'];
    $query = mysql_query("SELECT * FROM categoria order by nome")or die(mysql_error());
    while($res = mysql_fetch_array($query)){
    ?>
            <ul>
                <li><a href="#"> <?php echo $res['nome'];?> </a>
            <?php
              }
            ?>
    <?php
    include "../conexao.php";
    $nome = $_POST['nome'];
    $query = mysql_query("SELECT * FROM sub_categoria order by nome")or die(mysql_error());
    while($res = mysql_fetch_array($query)){
    ?>
                    <ul> 
                        <li><a href="prod_index_subcategoria.php?codsubcategoria=<?php echo $res['nome'];?>"><?php echo $res['nome'];?></a></li>
                    </ul>
                </li>
            <?php
              }
            ?>
            </ul>      

Below the structures of the tables used... Categories:

Subcategories:

Products:

If friends can help me by telling me where I’m going wrong, or even what to do to get to my goal, which are the Subcategories being listed within their respective Categories.

Waiting for the help of friends, and thanking everyone for their attention to my problem.

I’m editing the Post with the new line of code below with Inner Join...

        <?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 $res['nome_cat'];?> </a>
                    <ul> 
                        <li><a href="prod_index_subcategoria.php?codsubcategoria=<?php echo $res['nome'];?>"><?php echo $res['nome'];?></a></li>
                    </ul>
                </li>
            <?php
              }
            ?>
            </ul>

And I am also attaching the images of the structures of Categories and Sub-Categories that I made a mistake earlier and published the category in duplicity.

Category:

Sub-category:

Answer 1

ok... anyway: first create this function

//ler tabela
function DBRead($table, $param = null, $fields = "*"){
    $param  = ($param) ? " where {$param}": null;
    $query  = "SELECT {$fields} FROM {$table}{$param}";
    $result = DBExecute($query);
    if(!mysqli_num_rows($result))
        return false;
    else{
        while ($rs = mysqli_fetch_assoc($result)){
            $data[] =$rs;
        }
        return $data;
    }
}

//Executar query
function DBExecute($query, $id = false){
    $link   = DBConnect();
    $result = @mysqli_query($link,$query) or die (mysqli_error($link));

    if($id){
        $result = mysqli_insert_id($link);
        return $result;
    }

    DBClose($link);
    return $result;
}
//abre conexão
function DBConnect(){
    $link = @mysqli_connect('HOST','USERNAME','PASSWORD','DATABASE') or die(mysqli_connect_error());
    mysqli_set_charset($link, 'utf8') or die(mysqli_error($link));
    return $link;
}
//fecha conexão
function DBClose($link){
    @mysqli_close($link) or die(mysqli_error($link));
}

on your page put like this:

$cat = DBRead('cat');
foreach ($cat as $valueCat) {
    echo '<ul>';
        echo '<li>'.$valueCat['titulo'];
            echo '<ul>';
                $subcat = DBRead('subcat','fk_id ="'.$valueCat['id'].'"');
                foreach ($subcat as $valueSub) {
                    if($valueCat['id'] = $valueSub['fk_id']){
                        echo '<li>'.$valueSub['sub titulo'] .'<br/> </li>';
                    }
                }
            echo '</ul>';
        echo '</li>';
    echo '</ul>';

Note: don’t forget the includes!
Obs: cat = Category, Subcat = subcategory
The Primary key of the table Category must be Foreign in the table Subcategory ratio (1,N)
Ex:

Tab Cat          |  Tab Subcat
id 1             |  fk_id 1
                 |  fk_id 1
                 |  fk_id 1
id 2             |  fk_id 2
                 |  fk_id 2

etc... Good luck.

Answer 2

Have you considered adding an id_cat field (example), in which the id of the parent category, is inside the table subcategories? This way, you could select by calling only the subcategories according to the id of the parent category.

Answer 3

Dude in the sub category you need to have the code of the category. And in the SELECT of the sub category you place codigo_categoria = $res_categoria['id'] so you can create a relationship between the tables. Obs: You don’t need to call the connection more than once in the same file