View products from multiple categories

Asked

Viewed 134 times

2

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 ways:

CATEGORY 1

- Produto 1
- Produto 2

CATEGORY 2

- Produto 1
- Produto 2

I am doing it as follows (JUST AN EXAMPLE)

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

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

CATEGORY 1

- Produto 1

CATEGORY 2

- Produto 1

CATEGORY 2

- Produto 2

Someone could give me a light ?

php mysql relationship

3 answers

2


To bring in only one line, you can use the GROUP_CONCAT and use a delimiter to return the products. the Standard is to come the way it is coming even if you had 10 products for the same category, will return 10x the same category and different products, however if using the GROUP_CONCAT you return only 1 time to the category and the products separated by a delimiter to your choice, I did not understand if this is really what you need, but it would be 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 separator you want.

  • Hello, first of all thank you. I forgot to mention, I need to access other product columns, for example PRODUCT 1, I need to access the price, the description etc. In this way that you mentioned, I can only return a column that would be the [product], correct ?

  • can yes, I edited the answer

  • Thanks arrllondias, solved my problem.

  • You’re welcome @Jonas.

0

Just sort by category

If you just want to show first all the data of the X category products and after the Y and so on, you can make a simple select with order by

SELECT * FROM produdo ORDER BY fk_idCategoria

Divide 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 command above) create the beginning of a table and, by looping to fetch the database data, check if the category id has changed, in case closes the current table and opens another, here has an answer that does this

-1

Already 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
';

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

Browser other questions tagged php mysql relationship

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