{"id":149,"date":"2013-01-23T19:46:02","date_gmt":"2013-01-23T17:46:02","guid":{"rendered":"http:\/\/icefront.info\/wp\/?p=149"},"modified":"2013-01-23T19:46:02","modified_gmt":"2013-01-23T17:46:02","slug":"return-mysql-query-as-a-single-string","status":"publish","type":"post","link":"https:\/\/icefront.info\/index.php\/2013\/01\/23\/return-mysql-query-as-a-single-string\/","title":{"rendered":"Return MySQL query as a single string"},"content":{"rendered":"

I was playing with some statistics and wanted to display some queries as a single, comma-delimited text.<\/p>\n

Why one may need this? Since the result of the queries can be concatenated in an endless ways by (eg. PHP) code… The answer: to have one-line query returning directly the string \u2013 eg. for statistical purposes only.<\/p>\n

The data<\/h3>\n

Consider any type of data, for example:<\/p>\n

CREATE TABLE `test` (\n`id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n`name` varchar(50) COLLATE utf8_bin NOT NULL,\n`color` varchar(20) COLLATE utf8_bin NOT NULL,\n`quantity` int(10) unsigned NOT NULL,\nPRIMARY KEY (`id`));<\/pre>\n
Insert some data…<\/p>\n
INSERT INTO `test` (`id`, `name`, `color`, `quantity`) VALUES\n(1, 'Orange', 'orange', 5),\n(2, 'Apple', 'red', 25),\n(3, 'Strawberry', 'red', 15),\n(4, 'Banana', 'yellow', 12),\n(5, 'Lemon', 'yellow', 1),\n(6, 'Raspberry', 'red', 15),\n(7, 'Raspberry', 'red', 15),\n(8, 'Plum', 'blue', 5),\n(9, 'Blueberry', 'blue', 25),\n(10, 'Blue grapes', 'blue', 1),\n(11, 'Grapes', 'green', 1),\n(12, 'Kiwi', 'green', 5);<\/pre>\n
The solution: MySQL GROUP_CONCAT<\/h3>\n
To return any query as string, use the GROUP_CONCAT():<\/p>\n
SELECT GROUP_CONCAT(`name`) FROM `test`\n>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi<\/pre>\n
To alter the order of the results:<\/h4>\n
Doesn't work:\nSELECT GROUP_CONCAT(`name`) FROM `test` ORDER BY `name`;\n>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi\n\nDoes work:\nSELECT GROUP_CONCAT(`name` ORDER BY `name`) FROM `test`;\n>> Apple,Banana,Blue grapes,Blueberry,Grapes,Kiwi,Lemon,Orange,Plum,Raspberry,Raspberry,Strawberry<\/pre>\n
To enumerate distinct values:<\/h4>\n
SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test`;\n>> orange,red,yellow,blue,green<\/pre>\n
Ordering works like this:<\/p>\n
SELECT GROUP_CONCAT(DISTINCT `color` ORDER BY `color`) FROM `test`;\n>> blue,green,orange,red,yellow<\/pre>\n
Filtering<\/h4>\n
The WHERE clause must be specified at the end of the query, not inside the GROUP_CONCAT():<\/p>\n
SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test` WHERE `quantity`>10;\n>> red,yellow,blue<\/pre>\n
Specify a separator<\/h4>\n
For practical purposes, you may want a different separator. Use the SEPARATOR clause inside the GROUP_CONCAT(). Notice the spaces around the dashes:<\/p>\n
SELECT GROUP_CONCAT(DISTINCT `color` SEPARATOR ' - ') FROM `test` WHERE `quantity`>10;\n>> red - yellow - blue<\/pre>\n
Documentation<\/h4>\n
You may want to read more about GROUP_CONCAT()… Visit the official documentation page at dev.mysql.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
I was playing with some statistics and wanted to display some queries as a single, comma-delimited text. Why one may need this? Since the result of the queries can be concatenated in an endless ways by (eg. PHP) code… The answer: to have one-line query returning directly the string \u2013 eg. for statistical purposes only. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-149","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":0,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"wp:attachment":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}