Mysql and group_concat!

I'm determined to write a little more often on my blog (I've skipped 2008 altogether) because it also works as my personal notepad for the world to see. So let's get started.

Today I've discovered a wonderful new MySQL trick: group_concat and it saved me from doing tedious/slow scripting and also from trying to understand how Stored Procedures work in MySQL (which is something like arameic to me right now).

I've been working with Eventum which is a lovely open source bug tracker from the great thinkers at MySQL AB (now.. Sun). Eventum lets you add N custom fields to the issue creation and listing page, and it adds all of them to a table called eventum_issue_custom_fields. very much like this:


Each custom field has an Id, but they are all entered in different "rows" like this:
(lets say custon field "1" is the tv show name and field "2" is a short description)

IssueId	CustomFieldId	CustomFieldData
50	1	90210
50	2	Lame TV show
51	1	Family Guy
51	2	Funny Stuff

what if i wanted to select from this table the tv show name and the description in just one row?

Thinking about loops in your scripts?
Thinking about stored procedures?
Thinking it cannot be done at all?

Check this out:

SELECT IssueId,GROUP_CONCAT(CustomFieldData ORDER BY CustomFieldData SEPARATOR ',') AS value_list FROM tv_show_table GROUP BY IssueId ORDER BY IssueId

And you will get some nice stuff like:

IssueId	value_list
50	90210, Lame TV Show
51	Family Guy, Funny Stuff

How cool is that?!

the actual eventum query would be:

SELECT icf_iss_id,group_concat(icf_value order by icf_value separator ',') as values_list from eventum_issue_custom_field where icf_fld_id=2 OR icf_fld_id=1 group by icf_iss_id order by icf_iss_id;

Because i only want fields with id 1 or 2.


Bye!