CODEWITHSUNDEEP
phpmysqlgroup-concat
Joe Boris
Joe Boris

Reputation: 517

PHP MySQL GROUP_CONCAT working different than it should

I'm trying to get this query to return a list of product IDs, descriptions, and all of each product's ingredients. The query shown executes fine but returns only one record - the first product ID in the table, its corresponding description, and every ingredient in the ingredients table. The ingredients returned are grouped properly and in the right order, but they are all concatenated into one result. Example:

Product1 has ingredients A1, B1, C1 Product2 has ingredients A2, B2, C2, D2

And the result of the current query is:

[Product1_ID], [Product1_Description], "A1,B1,C1,A2,B2,C2,D2"

What I want is:

[Product1_ID], [Product1_Description], "A1,B1,C1" [Product2_ID], [Product2_Description], "A2,B2,C2,D2"

Am I going about this the right way? Here is my query:

SELECT TPD.intProductID AS ProductID, 
TD.strDescription AS Description, 
GROUP_CONCAT( TRH.strName SEPARATOR ', ' ) AS Ingredients 

FROM TProductsDescriptions AS TPD, 
TDescriptions AS TD, 
TRawHerbs AS TRH, 
TProductsIngredients AS TPI 

WHERE TPD.intDescriptionID=TD.intDescriptionID 
AND TPD.intProductID=TPI.intProductID
AND TPI.intIngredientID=TRH.intRawHerbID;

Upvotes: 1

Views: 290

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You need a group by statement to get what you want.

But, your query really needs more work than that. You need to learn proper join syntax (the use of the join keyword and on for the conditions):

SELECT TPD.intProductID AS ProductID, TD.strDescription AS Description, 
      GROUP_CONCAT( TRH.strName SEPARATOR ', ' ) AS Ingredients 
FROM TProductsDescriptions TPD join
     TDescriptions TD
     on TPD.intDescriptionID=TD.intDescriptionID join
     TProductsIngredients TPI
     on TPD.intProductID=TPI.intProductID join
     TRawHerbs TRH
     on TPI.intIngredientID=TRH.intRawHerbID 
group by TPD.intProductID;

The reason your query works is a quirk of MySQL. Most databases would generate an error because you have columns in the select that are neither in the group by clause nor in aggregation functions.

MySQL recognizes that the overall query is doing an aggregation. It then chooses arbitrary values for the first two columns, and concatenates all the ingredients together from all the products.

Upvotes: 0

Tom
Tom

Reputation: 6663

You need a GROUP BY clause. I would also recommend using joins, but that's your call.

SELECT TPD.intProductID AS ProductID, 
TD.strDescription AS Description, 
GROUP_CONCAT( TRH.strName SEPARATOR ', ' ) AS Ingredients 

FROM TProductsDescriptions AS TPD, 
TDescriptions AS TD, 
TRawHerbs AS TRH, 
TProductsIngredients AS TPI 

WHERE TPD.intDescriptionID=TD.intDescriptionID 
AND TPD.intProductID=TPI.intProductID
AND TPI.intIngredientID=TRH.intRawHerbID

GROUP BY TPD.intProductID

Upvotes: 2

Related Questions