[RESOLVED] MySQL: GROUP BY substring in column?

[ohnos]

Is there a way to do this?
Each "topic" may have multiple "subjects", I'd like to group by the subjects
The db table is like:

TOPIC 	         	SUBJECT
Abdominal Pain 	        Digestive System Topics|Symptoms
Abdominal Pregnancy 	Female Reproductive System|Pregnancy and Reproduction
Abnormalities 	        Birth Defects|Pregnancy and Reproduction
ABO Blood Groups 	Blood, Heart and Circulation

So when output it would be like:
Birth Defects

• Abnormalities

Pregnancy and Reproduction

• Abdominal Pregnancy
• Abnormalities

[ohnos]

so no other way but two queries?
1. query all subjects
2. explode
3. 2nd query GROUP BY LIKE?

That's the only way I can think to do it, as painfully inefficient as it may be.

[NogDog]

Probably the "best" solution is to normalize the data, so that if you want to treat "Birth Defects|Pregnancy and Reproduction" as two different subjects, then they should be in the database as separate rows. You might implement that without changing the current data by (though that would reduce the amount of normalization) by breaking out all the subjects into a separate table.

topics
======
topic_id  topic                subject
--------  -------------------  -----------------------------------------------------
1         Abdominal Pain       Digestive System Topics|Symptoms
2         Abdominal Pregnancy  Female Reproductive System|Pregnancy and Reproduction
3         Abnormalities        Birth Defects|Pregnancy and Reproduction
4         ABO Blood Groups     Blood, Heart and Circulation

subject_to_topic
================
subject                       topic_id
----------------------------  --------
Digestive System Topics        1
Symptoms                       1
Female Reproductive System     2
Pregnancy and Reproduction     2
Birth Defects                  3
Pregnancy and Reproduction     3
Blood, Heart and Circulation   4

Then you can use a join on that subject_to_topic table to get your grouping criteria. It should be relatively simple to create either a Db query or a PHP script to populate the new relational table from the main topics table, by explode()-ing the subjects from the original topic table and inserting rows in the new table for each explode()-ed part.