Skip to main content
A
Anonymous
February 11, 2008
Question

Grouping List Values from Different Database Columns

  • February 11, 2008
  • 4 replies
  • 891 views
Hi Folks,

I've hit a bit of a stumbling block on how best to achieve this.

I'm trying to output a list of categories for different products, however each field may contain a number of values, comma-separated. For example;

Row 1: category1,category2,category3
Row 2: category1, category4
Row 3: category5

The output I'm aiming for is;

category1
category2
category3
category4
category5

The field name is 'category_tags'.

In my head, I can visualize the method to simply output all the categories, but I can't quite get my head around cleaning them up and grouping them to prevent duplicates.

I'm using CF8 with MySQL as the Database.

Any help would be very much appreciated!
This topic has been closed for replies.

4 replies

A
Anonymous
February 13, 2008
At the present time, it's basically a shared database that I have no control over. I have requested a redesign of the database, but I fear that unless a reasonable number of other developers are requesting the same, it may take time before they actually do anything.
A
Anonymous
February 13, 2008
Thanks for the reply Gruizzly - you are indeed correct to assume that I am somewhat restricted by the nature of the data being provided to me.
P
paross1
Participating Frequently
February 13, 2008
quote:
....I am somewhat restricted by the nature of the data being provided to me.
After reading this, and your other statement "...this is how the data is provided to me, so without separating it before insert...", is this actually a matter of being stuck with a data model that you can not change, or is it that you just don't know how to fix it? If the data model is within your responsibility to control, you should absolutely correct it by normalization, otherwise you are going to be stuck trying to implement one kludge after another to accomplish what normally would be very easy to do with simple SQL statements.

Phil
D
Dan_Bracuk
Inspiring
February 11, 2008
Separate it before you insert it then. Also, fix your existing records.
D
Dan_Bracuk
Inspiring
February 11, 2008
Normalize your database and you won't have problems like this.
A
Anonymous
February 11, 2008
Unfortunately this is how the data is provided to me, so without separating it before insert, I'm stuck with it.
G
Grizzly9279
Participating Frequently
February 13, 2008
Dan is correct, a properly normalized database would allow you to perform this grouping naturally in the database. Generally speaking, you should NEVER store a comma-delimited list of anything in a relational database, since that prevents the database for manipulating or managing that data natively.

That being the case, if for some reason you have absolutely no choice but to work with someone else's crappy data model, there are some hack work-arounds which you can implement in ColdFusion. For example, if you have a recordset (lets call it "myQuery") that contains all of these records, including the "category_tags" column, you could do something like the following to build a list of unique categories.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices