0

I have a MySQL Routine having condition:

Where category like CONCAT("%",@category,"%")

...and this works fine with single category.

Now I want to compare with multiple categories, I tried with building string from PHP (string concatenation) but its not working (in Mysql Routine).

How can I convert this condition to match multiple LIKE Expression ?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Mark
  • 1,381
  • 5
  • 18
  • 21
  • Please paste what your tried for string concatenation – Shakti Singh Feb 28 '11 at 17:08
  • You'll have to explain how you are storing the `category` value -- looks like you're putting numerous category words into a single column (what we call "Denormalized Data" -- not good.) – OMG Ponies Feb 28 '11 at 17:08
  • How about http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337817#337817 ? – Konerak Feb 28 '11 at 17:09
  • I changed condition in routine like "Where @category" and passed 'category like "%cat1%" or category like "%cat2%"'. But its not working ... I guess MySQL routine is taking parameter as string like 'Where {String}' ... – Mark Feb 28 '11 at 17:10
  • How can I pass multiple category values to @category ? – Mark Feb 28 '11 at 17:22

1 Answers1

1

Assuming that your category column is like: "cats, dogs, elephants"; separated keywords. there are 3 solutions :

1) Ugliest solution, probably the worst :

WHERE category like CONCAT("%",@category1,"%") 
OR category like CONCAT("%",@category2,"%")

But for this you should know how many categories you are gonna query for. This way probably wont work at all.

2) If its a myisam table you can use full-text search functions to match against category field which would be much more faster and tidier than 1st option.

3) The best way is to redesign table structure to have a relation table between categories and subject item (since it seems like a 1-N relationship)

If you can give some definitions (codes) and data samples that would help understanding.

frail
  • 4,123
  • 2
  • 30
  • 38