Display products from various categories

1

This is my first post, I've been trying to solve a problem for some time now, but without success.

I have the CATEGORIES table and the PRODUCTS table

I need to display the following way:

CATEGORY 1

- Produto 1
- Produto 2

CATEGORY 2

- Produto 1
- Produto 2

I'm doing it this way (JUST AN EXAMPLE)

$var = SELECT * FROM categorias INNER JOIN produtos ON categorias.id = produtos.fk_idCategoria

But I'm lost when organizing when a category has more than 1 product, when I give print_r($var) it displays the following:

CATEGORY 1

- Produto 1

CATEGORY 2

- Produto 1

CATEGORY 2

- Produto 2

Could someone give me a light?

    
asked by anonymous 23.04.2018 / 22:17

3 answers

2

To bring in one line, you can use GROUP_CONCAT and use a delimiter to return the products. the pattern is coming the way it is coming even if you had 10 products for the same category, it will return 10x the same category and the different products, however if you use GROUP_CONCAT you only return 1x category and the separated products by a delimiter of your choice, I do not understand if this is what you need, but it would look like this:

$sql = "SELECT 
            c.*,
            p.*,
            GROUP_CONCAT([produto] SEPARATOR ',') AS produtos
        FROM categorias c 
        INNER JOIN produtos p ON categorias.id = produtos.fk_idCategoria
        GROUP BY c.id";

Where [product] is the field name of your products table and the ',' is the tab you want.

    
23.04.2018 / 23:03
0

Just sort by category

If you just want to first show all product data from category X and then from Y and so on, you can do a simple select with order by

SELECT * FROM produdo ORDER BY fk_idCategoria

Split by category

But if you want to do a division by category, for example, create a table by category you can (in addition to the above command) create the beginning of a table and, when looping to fetch the database data, check if the category id has changed, in case it closes the current table and opens another, here has an answer that does this

    
24.04.2018 / 03:17
-1

Have you tried to sort your query by Category Id?

$var = '
    SELECT * 
    FROM categorias 
    INNER JOIN produtos ON categorias.id = produtos.fk_idCategoria 
    ORDER BY produtos.fk_idCategoria
';

In this way, your print_r will probably return something closer to what you need, but you will need to control how to display the data with logical tests and repetition structures.

    
24.04.2018 / 15:25