Author Archive

How to show a column result in a one line comma separated list in MySql

Often I need to extract a list of articles IDs from my MySql databases but getting the result in the default column is not enough and it can be easier to convert the result from a column into a one line list of comma separated results, for instance from

Select id from myTable

but your expected output is 64, 85, 25, 63

To concatenate multiple rows results into a single mySql field you can use the group_concat built-in function (to make sure your MySql version has this function you would like to check in the manual).

Let’s see a real life example query where we can apply this function. In my db I have a list of additional information items identified by ID related to a specific website entry/profile. The query below allows me to collect all the IDs of the entries that relate to ‘www.example.com’

SELECT GROUP_CONCAT(additional_information_id)
FROM  `company_profile_additionalinformation`  WHERE  profile_website ='www.example.com' ;

Please note the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. This can be changed. Check the manual for more information .