Get Column Values As Comma Separated String In MySQL

While working on a project I was required to display the data fetched from a column in form of comma separated values. With couple of Google searches I was able able to land on a solution.

You can easily perform this task using MySQL’s GROUP_CONCAT function.

Let’s assume you have a books table and you are using following query to fetch all book ids from database where author’s id is 4.

SELECT id FROM `books` WHERE author_id = 4

The above query will produce following result.

id
1
2
5
9
12
15
18
20

And our desired output is as follows.

"1,2,5,9,12,15,18,20"

In order to produce the above output you can user following query.

SELECT GROUP_CONCAT(id) FROM `books` WHERE author_id = 4 GROUP BY author_id

This function concatenates multiple strings from a group into a single string.

For more information visit: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Quick Tip: How to convert a negative number to a positive number using PHP?

Leave a Comment.