Subcategories within their respective Category in the menu

0

Hello, I've created a menu that looks for the information in the database. I have a table with the categories and another one with the subcategories, so my beauty is that it is difficult to have each subcategory listed in its category (example below) ...

ButI'mnotgettingit,itlooksliketheexamplebelow...

BelowI'mattachingthecodeusedforthemenu...

<?phpinclude"../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 table structures used ... Categories:

Subcategories:

Products:

IffriendscanhelpmebytellingmewhereI'mgoingwrong,orevenwhattodosothatIcanreachmygoal,whichareSubcategoriesbeinglistedwithintheirrespectiveCategories.

Iamwaitingforthehelpofmyfriends,andthankyouforyourattention.

I'meditingthePostwiththenewlineofcodebelowwithInnerJoin...

<?phpinclude"../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'm also attaching the images of the Categories and Sub-Categories structures that I got wrong before and published the category duplicity.

Category:

Sub-Category:

    
asked by anonymous 08.03.2016 / 00:00

3 answers

0

Have you ever thought about adding a cat_id (example) field, where the id of the parent category was inside the subcategory table? In this way, you could do the select by calling only the subcategories according to the id of the parent category.

    
08.03.2016 / 01:37
0

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

    
08.03.2016 / 03:09
0

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 it 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: Do not forget to includes ! Note: cat = Category, subcat = subcategory
The Primary key of the Category table must be Foreign in the Subcategory table relation (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.

    
08.03.2016 / 03:04