views:

176

answers:

1

Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output. Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL...

So to try and explain:

I have three tables.

  • Categories
  • Articles
  • CategoryArticles_3NF

A category contains CategoryID + titles, descriptions etc. It can contain any number of articles in the Articles table, consisting of ArticleID + a text field to house the content. The CategoryArticles table is used to link the two, so contains both the CategoryID and the ArticleID.

Now, if I select a Category record, and I JOIN the Articles table via the linking CategoryArticles_3NF table, the result is a separate row for each article contained within that category. The issue is that I want to output one single row for each category, containing content from all articles within.

If that sounds like a ridiculous request, it's because it is. I'm just using articles as a good way to describe the problem. My data is actually somewhat different.

Anyway - the only way I can see to achieve this is to use a 'GROUP_CONCAT' statement to group the content fields together - the problem with this is that there is a limit to how much data this can return, and I need it to be able to handle significantly more.

Can anyone tell me how to do this?

Thanks.

A: 

This sounds like something that should be done in the front end without more information.

If you need to, you can increase the size limit of GROUP_CONCAT by setting the system variable group_concat_max_len. It has a limit based on max_allowed_packet, which you can also increase. I think that the max size for a packet is 1GB. If you need to go higher than that then there are some serious flaws in your design.

EDIT: So that this is in the answer and not just buried in the comments...

If you don't want to change the group_concat_max_len globally then you can change it for just your session with:

SET SESSION group_concat_max_len = <your value here>
Tom H.
Usually this is done in the front end, but for this specific issue I need it done via MySQL.I'm running on a shared server, and therefore have no access to change the group_concat_max_len or max_allowed_packet settings.The data will probably never exceed about 100kb.
Gary
I believe that you can set the group_concat_max_len for just your session. Try "SET SESSION group_concat_max_len = <your value here>"
Tom H.
Right you are, that seems to work... thanks!In the end, as time was a factor, I did it with server-side coding rather than MySQL - it's a performance issue and certainly not the way I'd wanted to handle the problem... but won't be noticable on an app this size.
Gary