MySQL GROUP_CONCAT

The GROUP_CONCAT function is used in MySQL to concatenate column data from a particular grouping into a string, rather than performing this action inside a client side script. For instance, if you have a table of large cities along with their state, you could do this:

SELECT state, city
FROM bigcities

which would return

+-------+--------------+
| state | city         |
+-------+--------------+
| OH    | Cleveland    |
| OH    | Columbus     |
| OH    | Cincinnati   |
| PA    | Pittsburgh   |
| PA    | Philadelphia |
...

and then process each row and group them by state within your PHP script. Or you could use GROUP_CONCAT as follows:

SELECT state, GROUP_CONCAT(city) AS cities
FROM bigcities
GROUP BY state

which would return:

+-------+--------------------------------+
| state | cities                         |
+-------+--------------------------------+
| OH    | Cleveland,Columbus,Cincinnati  |
| PA    | Pittsburgh,Philadelphia        |
...

The default separator is a comma, but you can define an alternative separator as follows:

SELECT state, GROUP_CONCAT(city SEPARATOR ' -- ') AS cities
FROM bigcities
GROUP BY state

which would return

+-------+--------------------------------------+
| state | cities                               |
+-------+--------------------------------------+
| OH    | Cleveland -- Columbus -- Cincinnati  |
| PA    | Pittsburgh -- Philadelphia           |
...