View Products Subcategory

Question

I’m making a virtual store, but I’m not able to display the products of the subcategory on the page.

Category products will be normal, but sub-category products will not.

Follows the code:

<?php 
    $categoria = $_GET['categoria'];
    $categoria_sub = $_GET['categoria_sub'];
    $sql = mysql_query("SELECT * FROM produto
                         INNER JOIN categoria
                            ON produto.categoria = categoria.categoria_id
                         INNER JOIN categoria_sub
                            ON produto.categoria_sub = categoria_sub.sub_cat_id                                                     
                         WHERE produto.categoria = '".$categoria."' 
                           AND produto.categoria_sub = '".$categoria_sub."'
                        ORDER BY RAND()");
    if (mysql_num_rows($sql)== true) {
        while ($ln = mysql_fetch_assoc($sql)) {
?>

Tables:

PRODUCT

CATEGORY

SUBCATEGORY

Answer 1

If a sub-category belongs to a category, it is not necessary to have one fk for category and one for sub-category in the product register, it is sufficient to have the sub-category. The same applies to WHERE where if you filter by sub-category, automatically the category will be filtered by the condition c.categoria_id = s.sub_cat_categoria.

Having:

$categoria = $_GET['categoria'];
$categoria_sub = $_GET['categoria_sub'];

Follows the code:

SELECT 
* 
FROM produto p
INNER JOIN categoria_sub s on s.sub_cat_id = p.categoria_sub 
INNER JOIN categoria c on c.categoria_id = s.sub_cat_categoria
WHERE p.categoria_sub = '".$categoria_sub."'
ORDER BY RAND()

If you want to filter by category only:

Using the key you have in the product table:

SELECT 
* 
FROM produto p
INNER JOIN categoria_sub s on s.sub_cat_id = p.categoria_sub 
INNER JOIN categoria c on c.categoria_id = s.sub_cat_categoria
WHERE p.categoria = '".$categoria."'
ORDER BY RAND()

Using the related key in the sub-category:

SELECT 
* 
FROM produto p
INNER JOIN categoria_sub s on s.sub_cat_id = p.categoria_sub 
INNER JOIN categoria c on c.categoria_id = s.sub_cat_categoria
WHERE c.categoria_id = '".$categoria."'
ORDER BY RAND()

UPDATE

Using the related key in the sub-category (without filtering) if nothing is passed in the GET category:

$where_categoria = '';

if($categoria != '') {
    $where_categoria = "WHERE c.categoria_id = '".$categoria."'";
}

SELECT 
* 
FROM produto p
INNER JOIN categoria_sub s on s.sub_cat_id = p.categoria_sub 
INNER JOIN categoria c on c.categoria_id = s.sub_cat_categoria
".$where_categoria."
ORDER BY RAND()