Return Attribute Group for a specific category

Question

Return Attribute Group for a specific category

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have 3 tables: ( link )

tb_attributes, tb_atr_to_prod, tb_atr_to_cat.

The TB_ATRIBUTOS table has all parent and child attributes. The TB_ATR_TO_PROD table has all the attributes with their respective products. The TB_ATR_TO_CAT table has the category code that represents a certain set of attributes. I need to create a list of all the attributes of a certain category, something like this:

<h3>Cor</h3>
<ul>
  <li>Verde</li>
  <li>Azul</li>
  <li>Preto</li>
</ul>

<h3>Cone</h3>
<ul>
  <li>2.500M</li>
  <li>3.000M</li>
  <li>1.000M</li>
</ul>

php mysql

asked by anonymous 31.05.2016 / 21:33

2 answers

1

First I executed this query to bring your complete list and analyze your request:

SELECT DISTINCT
    prd.id_prod as ID_PRODUTO,
    att.id as ID_ATRIBUTO,
    att.id_pai,
    att.nome_atributo,
    prd.id AS ID_TAB_PRD
FROM
    tb_atributo att
        LEFT JOIN
    tb_atr_to_prod prd ON (prd.id_atr = att.id)
WHERE
    prd.id IS NOT NULL

GROUP BY att.id , prd.id_prod

However, for you to do what you want, you need to use a technique called PIVOT:

Here is a simple example

And here's an example, more complex

31.05.2016 / 23:22

Sucess Ajax does not work with Controller Codeigniter You are giving error in the array using the while loop

score 1 · Accepted Answer

Follow new select, with the restrictions you reported in the comments. See if you can resolve your issue.

SELECT r.*, cat.id_cat
  FROM (  SELECT a.id AS id_pai,
                 '1 - Categoria' AS rotulo,
                 a.nome_atributo AS valor
            FROM tb_atributo a
           WHERE id_pai = 0
           UNION
           SELECT b.id_pai, 
                 '2 - Atributo' AS rotulo,
                  b.nome_atributo AS valor
             FROM tb_atributo b
            WHERE b.id_pai <> 0  ) AS r
 INNER JOIN tb_atr_to_cat cat ON r.id_pai = cat.id_atr
 INNER JOIN (SELECT DISTINCT 
                    a.id_pai
               FROM tb_atr_to_prod p
              INNER JOIN tb_atributo a ON p.id_atr = a.id) ap ON r.id_pai = ap.id_pai
 WHERE cat.id_cat = 115
 ORDER BY 1, 2, 3

Unfortunately I could not test in sqlfiddle. Get back to me after your tests.

PREVIOUS ANSWER

Friend, follow a solution that returns a list according to your example:

SELECT r.*
  FROM (  SELECT a.id,
                 '1 - Categoria' AS rotulo,
                 a.nome_atributo AS valor
            FROM tb_atributo a
           WHERE id_pai = 0
           UNION
           SELECT b.id_pai, 
                 '2 - Atributo' AS rotulo,
                  b.nome_atributo AS valor
             FROM tb_atributo b
            WHERE b.id_pai <> 0  ) AS r
 ORDER BY 1, 2, 3

Just use the SELECT column and mount a display logic on your rotulo page.

I hope I have helped.