What is MySQL’s GROUP_CONCAT function for?

How often have you wanted a comma separated list of values in a table of results. Say you had a list of trainers in makes, and sizes? And you want to display this list as a grid:

Make   | Size
----------------------
Adidas | 6
Adidas | 7
Adidas | 8
Adidas | 10
Nike   | 7
Nike   | 8
Puma   | 5
Puma   | 7
Puma   | 10
Puma   | 12

Now, if these records are in one table, how are you going to get the sizes for each trainer?

You could loop through each group of trainers, and output a string, size by size. Or, you could use the GROUP_CONCAT function in MySQL, which will give you a list of the sizes as one field, even though they are multiple records.

SELECT make,
GROUP_CONCAT(DISTINCT size ORDER BY size ASC SEPARATOR ", " ) AS trainer_size
FROM trainers GROUP BY make ASC;

Then all you need is to loop through the 3 records, of each will contain 2 fields: make and trainer_size. Its a lot easier than writing code to loop through the DISTINCT makes and get the available sizes.

By admin

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.