CODEWITHSUNDEEP
phpmysqlsearchtags
simultsop
simultsop

Reputation: 800

Match tags in MYSQL

I wanted to know how can you search in tags p.s. I have a table where there is a column for tags "bar,barmen,drink".

In search for example people search for barmen drink, how do I match this, I tried LIKE, INSTR() but no luck, the point is that tags column content doesn't have spaces between. It's like "bar,barmen,drink" not like "bar, barmen, drink"

Any help..?

Upvotes: 0

Views: 325

Answers (2)

Doug Kress
Doug Kress

Reputation: 3537

This is a problem inherent with this type of design. Short of changing how you store the data, you'd have to do a search like this:

SELECT * FROM my_table WHERE CONCAT(',', tags, ',') LIKE '%,bar,%';

EDIT: Actually, I just double-checked, and you can use regexp like so:

SELECT * FROM my_table WHERE tags REGEXP '[[:<:]]bar[[:>:]]';

Which should be quite a bit faster.

Upvotes: 2

Delan Azabani
Delan Azabani

Reputation: 81482

Why not have a separate tags table, with

id int(11) auto_increment
post_id int(11)
tag text

Then, have one tags row for each tag.

Upvotes: 4

Related Questions