A Product in Multiple Categories - PHP

Asked

Viewed 1,339 times

7

I would like to know how to relate a product in several categories ? For example: A shirt can be in the Categories : "Green / Blue / Yellow" and when I filtered by category the product should appear in the category "Green , blue or Yellow".

I know I should use Relationship N : N, but how do I apply this to the PHP code? There is another way to do it without using the N:N relationship, for example by array ?

php mysql sql relationship

  • I would use this as a color and not a category, but that’s okay. It didn’t quite clear the "various categories" of your question. In the case a product can be in more than one, e.g.: green and blue?

  • It’s not a PHP problem, it’s a database problem. @Zuul gave a great response illustrating Many-to-Many in the database you use, Mysql. Other banks like Postgre accept arrays, and even with Mysql you have the option to save the Color Ids in a single field, but in the case of Mysql this would greatly harm future queries. About PHP itself, you can use arrays or whatever you want. I would suggest @camisa['cores'] = [QUERY PARA PEGAR AS CORES COM INNER JOIN];

1 answer

13


You do not indicate the structure of your tables, but for this purpose you should make use of an intermediate table that will effectively serve the purpose of relating each product to multiple categories:

EER diagram

ERR Diagram

This way, you have a table for the products, a table for the categories and a table to establish the relationship between the two.

Practical example

For your case, the product "shirt" has 3 categories, the "Green", "Blue" and "Yellow", so in the table produtos_categorias he would have three records:

Table: Products

Tabela Produtos

Table: Categories

Tabela Categorias

Relation Table: Products ~ Categories

Tabela de relação entre produtos e categorias

This way, by choosing the category azul would carry out a consultation of the type:

SELECT 
    produtos.nome
FROM produtos_categorias
INNER JOIN produtos ON (produtos.id = produtos_categorias.produtos_id)
INNER JOIN categorias ON (
    categorias.id = produtos_categorias.categorias_id
    AND categorias.nome='azul'
)

That returns the products that are in the category azul.

The answer is a practical example of abstract form, it does not reflect performance nor a correct functioning for the application to develop. Only the procedure to be adopted to deal with the problem at hand is illustrated.

Browser other questions tagged php mysql sql relationship

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