PHP MySQL GROUP_CONCAT working different than it should

Question

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;

Tom · Accepted Answer · 2013-05-29 20:13:10Z

2

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

answered May 29, 2013 at 20:13

Tom

6,6633 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-05-29 20:15:10Z

0

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.

answered May 29, 2013 at 20:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Joe Boris Over a year ago

Thanks for the advice. I'm new to SQL clearly, but my college professor advised against using the join keyword. I forget why, though

Gordon Linoff Over a year ago

@JoeBoris . . . Either you misunderstood your professor or s/he was wrong. The join key word is a bona fide part of SQL standard since 1992. It also allows you to do left, right, and often full joins, which are not easily done with the implicit join syntax.

Joe Boris Over a year ago

Ok, I'll study up on this syntax and start doing joins this way instead I guess. Thanks for your help

Collectives™ on Stack Overflow

PHP MySQL GROUP_CONCAT working different than it should

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related