www.webdeveloper.com
Results 1 to 5 of 5

Thread: match numbers in comma separated values fields

  1. #1
    Join Date
    Mar 2009
    Posts
    452

    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
    Posts
    2,984
    What's your table structure. From your post, I am assuming it looks like this (I could be wrong):

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

    Code:
    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!
    I've switched careers...
    I'm NO LONGER a scientist,
    but now a web developer...
    awesome.

  3. #3
    Join Date
    Mar 2009
    Posts
    452
    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
    Posts
    2,984
    Oh no!!! I made a terrible mistake, I meant to say the following in my post above

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

    Code:
    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.
    I've switched careers...
    I'm NO LONGER a scientist,
    but now a web developer...
    awesome.

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

    Ive found a solution

    Code:
    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 2 users browsing this thread. (0 members and 2 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