8

I have a product table which contains a field called 'categories' to save product related category ids as comma separated values. I am using regexp to search products from a category.

Assume there is record containing 4,24,1,31 my expression is,

..WHERE categories REGEXP ',?4,?'

but this returns both product of category 4 and 24

I just need to display only category 4.

Am I missing something?

Tim Pietzcker
  • 328,213
  • 58
  • 503
  • 561
vinu
  • 658
  • 10
  • 20
  • 3
    Why would you save relations like that? Why don't you use another table? – Glavić Sep 24 '12 at 06:32
  • i was. but i have lots of products and every of those products are related to two or more categories. so it makes a little huge table. – vinu Sep 24 '12 at 06:34
  • So? This is the correct way to do stuff in database. Now you have to do REGEXP on huge table, do you think that is faster? – Glavić Sep 24 '12 at 06:37
  • I thought it is okay than using a separate table. Oh i am confused now.Did this way make me frustrate in future? – vinu Sep 24 '12 at 06:44
  • You have limited yourself. What will happend, when you must find products that have: a) catId 1 and 2, b) catId 1 or 2, c) catId from 1 to 3, etc... Just make table "product_mtm_category" that has field "product_id and category_id". – Glavić Sep 24 '12 at 07:11

1 Answers1

13

Use

WHERE categories REGEXP "(^|,)4(,|$)"

This matches 4 if surrounded by commas or at the start/end of the string.

In your present version, both commas are entirely optional, so the 4 in 24 matches.

Tim Pietzcker
  • 328,213
  • 58
  • 503
  • 561