Subcategories within their respective Category in the menu

Asked

Viewed 973 times

3

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)...

inserir a descrição da imagem aqui

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

inserir a descrição da imagem aqui

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: inserir a descrição da imagem aqui

Subcategories: inserir a descrição da imagem aqui

Products: inserir a descrição da imagem aqui

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:

inserir a descrição da imagem aqui

Sub-category:

inserir a descrição da imagem aqui

  • could post the table structure category and subcategories?

  • OK, Hebert de Lima, I just edited the Post with the structures used. I hope it helps. Hugs.

3 answers

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.

  • Hello Hebert, I tried to follow your suggestion in creating an Inner Join, it almost worked, but the categories are duplicating, and I don’t know how to make the Sub-Categories stay within each Categories referring to them. I’m posting the address http://www.efacil.com.br/prod_index.php, for you. can check what is happening with the Categories menu, and I will also post the structures correctly Category and Sub, thanks? Big hug and thank you for your attention and help to my problem.

0

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.

0

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.