www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] MySQL: GROUP BY substring in column?

Hybrid View

  1. #1
    Join Date
    Nov 2011
    Posts
    9

    resolved [RESOLVED] MySQL: GROUP BY substring in column?

    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:
    Code:
    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

  2. #2
    Join Date
    Nov 2011
    Posts
    9
    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.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,251
    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.

    Code:
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles