ICE786 Blog We train the people
Today we will solve a problem related to MySQL query. Here is a problem:-
Let’s suppose you have a table named “Items” and it has three fields and following data:-
| Id | Item_name | Price |
| 1 | Item 1 | 10 |
| 2 | Item 2 | 20 |
| 3 | Item 3 | 30 |
Now you want to write a query that will get all values from “Items” column and concatenate it putting a comma between every item name like this “Item 1, Item 2, Item 3”. You often need this type of data when you want to generate graph/chart. In the normal query you will write something like this:
|
1 2 3 4 5 6 |
SELECT items.item_name FROM Items WHERE id > 0 |
And it will produce this type of data
| Item_name |
| Item 1 |
| Item 2 |
| Item 3 |
But it is not our demand we need this kind of data “Item 1, Item 2, Item 3”. So now what will you do when you received this data. You surely write some PHP function that will grab every item and then save it in an array and then put a comma and then get next item name and so on. I think this approach is not good. I have a solution for you. There is a function in MySQL named “GROUP_CONCAT”. This function will grab data from the column and will generate the concatenated result. Let’s see in action. Write this query:-
|
1 2 3 4 5 |
SELECT GROUP_CONCAT(item_name) FROM items WHERE id > 0 |
and it will generate this type of data “Item 1,Item 2,Item 3” (without quotes). But wait a minute, this function does more for you. It has 2nd parameter where you will set separate for your data, see this query:-
|
1 2 3 4 5 |
SELECT GROUP_CONCAT(item_name SEPARATOR '+') FROM items WHERE id > 0 |
and it is generate this kind of data “Item 1+Item 2+Item 3”. The second parameter will tell this query to put “+” as a SEPARATOR. You can write any symbol in place of “+” in above example.
Good luck and Happy Coding.
