match numbers in comma separated values fields

[ZABI]

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...

[aj_nsc]

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!

[ZABI]

yes you guessed absolutely right... how can i select product(s) that have 11 in their categoryID for example.

[aj_nsc]

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.

[ZABI]

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