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...
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:
Making the queries much easier.
Let me know if I've misunderstood anything and sorry!
yes you guessed absolutely right... how can i select product(s) that have 11 in their categoryID for example.
Oh no!!! I made a terrible mistake, I meant to say the following in my post above
Then all you have to do is
My sincerest apologies for the confusion.
select productID from `table` where categoryID = 11;
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.
the problem was i had to maintain the tables structure. thanx for your efforts anyway
Ive found a solution
i cant understand that regexp pattern but it works for me
SELECT * FROM products WHERE categories REGEXP '[[:<:]]8[[:>:]]'
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread