There is the following table structure.

**Products:** -id -title -price **Equipments** -id -title **Eqvalues** -id -equipment_id -value **Equipment_product_value** -id -product_id -equipment_id -value_id

I want to display data in a format.

 |products.title|products.price|equipments.title-1|eqvalues.value-1|equipments.title-N|eqvalues.value-N

In simple words, a product has characteristics (equipments) and the values ​​of these characteristics (values) to be output. Is it possible to do so. My part of the implementation:

 SELECT products.id, products.title, products.status, products.price, products.info, product_photo.filename, equipments.title AS etitle, values.value FROM products INNER JOIN product_photo ON products.id = product_photo.product_id INNER JOIN equipment_product ON products.id = equipment_product.product_id INNER JOIN equipments ON equipment_product.equipment_id = equipments.id INNER JOIN `values` ON equipment_product.value_id = values.id GROUP BY products.id
  • This is called a PIVOT. - Akina
  • one
    there are some tables in the question, others in the query. And what exactly does not work for you? What's wrong? - Jurij Jazdanov

1 answer 1

It looks like you have MySQL .

In general, it is impossible to display each property in several columns. You do not initially know how many lines there can be.

But you can display all the properties of the product in one column with a separator |, using the GROUP_CONCAT function:

 SELECT products.id, products.title, products.status, products.price, products.info, product_photo.filename, GROUP_CONCAT( DISTINCT CONCAT(equipments.title, '|', values.value) ORDER BY equipments.title SEPARATOR '|' ) AS prodInfo FROM products INNER JOIN product_photo ON products.id = product_photo.product_id INNER JOIN equipment_product ON products.id = equipment_product.product_id INNER JOIN equipments ON equipment_product.equipment_id = equipments.id INNER JOIN `values` ON equipment_product.value_id = values.id GROUP BY products.id
  • Yes mysql. Initially, I also thought to use GROUP_CONCAT, only two columns. In principle, this option is suitable. PHP will correct :) - depredator
  • just how to get rid of duplicates, and then 2 times each entry. - depredator
  • @depredator, add DISTINCT, added in response. However, it is strange that you have duplicates, in theory, with such a structure should not be, some condition may not be enough. - pegoopik
  • yes DISTINCT - fixed everything. - depredator