Display products Subcategory

Question

Display products Subcategory

Navigation

#1 by (1 votes)

0

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

The products of the category go normal, but the products of the subcategory do not.

Follow 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

1 answer

How to remove special character and column point string from a data frame? .position () giving error - JQuery

score 1 · Accepted Answer

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

Having:

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

Follow 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 only by category:

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 (unfiltered) subcategory, 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()