MySQL String conversion to Expression

Question

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 ?

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
– OMG Ponies, Commented Feb 28, 2011 at 17:08
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
– Mark, Commented Feb 28, 2011 at 17:10

frail · Accepted Answer · 2011-02-28 17:30:52Z

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.

answered Feb 28, 2011 at 17:30

frail

4,1282 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL String conversion to Expression

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related