Using GROUP CONCAT in MySQL

Added on: Thursday 30th July 2009

Yesterday I had the need to get a list of unique values from a MySQL database where the values were stored as a comma delimited set. There were several ways to approach this but I wanted to try to minimise the amount of processing required.

Good database design says that it would be better to store the values in a table that links the value to a record in another table - allowing multiple values for each record - but I was stuck with the current arrangement so had to make do.

The particular field in the database could contain a single value such as item1 or multiple (comma separated) values in any order such as item2,item1 or item1,item3

The first thing that occurred to me was just to retrieve the records in question and then use PHP to loop through each one, splitting them into individual values. The code would then add the value to an array if it wasn't already in there or ignore it if it was. Finally it would need to sort the array.

This only involves a single call to MySQL but there is still a lot of processing for PHP.

I then decided to make use of the GROUP_CONCAT function in MySQL as follows:

SELECT GROUP_CONCAT(items ORDER BY items SEPARATOR ',') AS items

This produces a single row with a field containing a comma separated list of all possible combinations eg. item1,item1,item3,item2,item1 from the example above.

By making use of the array_unique function in PHP there are only three lines of code required to create the unique sorted list.

$ary = explode(",", $items);
$tags = array_unique($ary);
sort($tags);

I am assuming in the above code that $items is the value of the field resulting from the MySQL query. The script then uses explode to create an array of values - array_unique removes any duplicates and the sort function ensures they are in alphabetical order.

Permalink