How to merge attributes and values ​​in EAV data model?

Question

PHP

Let me explain. There are three tables products, attributes and values. I'm trying to get a product with its attribute and value with such a request.

SELECT 
  `prod`.`name`, 
  `prod`.`price`, 
GROUP_CONCAT(`atr`.`nameAttribute`) as attributeName,
GROUP_CONCAT(`val`.`attributeValue`) as attributeValue
FROM `products` as `prod`
LEFT JOIN `productAttribute` as `atr` ON `prod`.`idProduct` = `atr`.`idProduct`
LEFT JOIN `attributeValue` as `val` ON `atr`.`idAttribute` = `val`.`idAttribute`
WHERE `prod`.`idCategory` = :idCategory
GROUP BY prod.idProduct;

The result is:
[name] =
> ProductName [price] => $9.99
[attributeName] => AttributeName1, AttributeName1, AttributeName3
[attributeValue ] => AttributeValue1, AttributeValue2, AttributeValue3
Only this result was achieved using the GROUP_CONTACT statement. Is there any way to display attributes like AttributeName1=AttributeValue1, AttributeName2=AttributeValue2?
Thank you very much in advance!

Answer 1

SELECT `prod`.`name`, `prod`.`price`, 
       GROUP_CONCAT(CONCAT(`atr`.`nameAttribute`, '=' , `val`.`attributeValue`) as `attribute`,
FROM `products` as `prod`
LEFT JOIN `productAttribute` as `atr` ON `prod`.`idProduct` = `atr`.`idProduct`
LEFT JOIN `attributeValue` as `val` ON `atr`.`idAttribute` = `val`.`idAttribute`
WHERE `prod`.`idCategory` = :idCategory
GROUP BY `prod`.`idProduct`;