Results 1 to 5 of 5

Thread: match numbers in comma separated values fields

  1. #1
    Join Date
    Mar 2009

    Red face match numbers in comma separated values fields

    hi all, i m trying to get records matching a number in a comma separated values in a field

    i have a table in which products details are stored and a field stored ID's of categories in which that product should be listed,

    for example a product is to be listed in 5 categories having ID's 1,5,8,15,88. so if i want to match a product that should go in category with id 8

    how can i do that... it must match a product having value 8 not 18, 88 or so...

  2. #2
    Join Date
    Dec 2005
    What's your table structure. From your post, I am assuming it looks like this (I could be wrong):

    productID			productName			categories
    1				blah				1,2,11
    2				blah2				3,5,6
    3				blah3				2,7,9
    4				blah4				2,7,9,11

    If that is the case (and I'm not saying it is, but it's just what I've gathered from the post) then you've got some normalization to do.

    It should look like this:

    productID			productName
    1				blah						
    2				blah2					
    3				blah3					
    4				blah4					
    productID			categoryID
    1				1,2,11
    2				3,5,6
    3				2,7,9
    4				2,7,9,11
    Making the queries much easier.

    Let me know if I've misunderstood anything and sorry!

  3. #3
    Join Date
    Mar 2009
    yes you guessed absolutely right... how can i select product(s) that have 11 in their categoryID for example.

  4. #4
    Join Date
    Dec 2005
    Oh no!!! I made a terrible mistake, I meant to say the following in my post above

    productID			categoryID
    1				1
    1                              2
    1                              11
    2				3
    2                               5
    2                               6
    3				2
    3                               7
    3                               9
    4				2
    4                              7
    4                              9
    4                              11

    Then all you have to do is

    select productID from `table` where categoryID = 11;
    My sincerest apologies for the confusion.

    So, as this might not answer your question directly, please pull the plug on the way your database is structured now (with catID's in a comma-separated field) and 'normalize' it like the way I have posted above. You'll save yourself tens, if not hundreds, of hours of headaches if you do.
    Last edited by aj_nsc; 12-12-2011 at 12:20 PM.

  5. #5
    Join Date
    Mar 2009
    the problem was i had to maintain the tables structure. thanx for your efforts anyway

    Ive found a solution

    SELECT * FROM products WHERE categories REGEXP '[[:<:]]8[[:>:]]'
    i cant understand that regexp pattern but it works for me

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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