MySQL Select Like

Question

I've got a field in my table for tags on some content, separated by spaces, and at the moment I'm using:

SELECT * FROM content WHERE tags LIKE '%$selectedtag%'"

But if the selected tag is 'elephant', it will select content tagged with 'bigelephant' and 'elephantblah' etc... How do I get it to just select what I want precisely?

Thanks!

Answer 1

I would consider a different design here. This constitutes a many-to-many relationship, so you could have a tags table and a join table. In general, atomicity of data saves you from a lot of headaches.

An added bonus of this approach is that you can rename a tag without having to edit every entry containing that tag.

Answer 2

The '%' is a wildcard in SQl. Remove the wildcards, and you will get precisely what you ask for.

Answer 3

SELECT * FROM content WHERE tags RLIKE '[[:<:]]elephant[[:>:]]'

If your table is MyISAM, you can use this:

SELECT * FROM content WHERE MATCH(tags) AGAINST ('+elephant' IN BOOLEAN MODE)

, which can be drastically improved by creating a FULLTEXT index on it:

CREATE FULLTEXT INDEX ix_content_tags ON content (tags)

, but will work even without the index.

For this to work, you should adjust @@ft_min_wold_len to index tags less than 4 characters long if you have any.

Answer 4

Redo the table design but for now if you use spaces to delimit between tags you COULD do this:

SELECT * FROM content WHERE tags LIKE '% elephant %';

Just make sure that you lead and end with a space as well (or replace the spaces with commas if you're doing it that way)

Again though, the best option is to set up a many-to-many relationship in your database but I suspect you're looking for a quick and dirty one-off fix.

Answer 5

A: You have to create a separate tags table which points to the content with contentid and contains a keyword, then:

select a.*
from content a,tags b
where a.id=b.contentid
group by a.id;

B: Put a comma between the tags and befor and afther them, like ",bigelephant,elephant,", then use like "%,elephant,%"

Answer 6

WHERE tags LIKE '% '{$selectedtag}' %'
      OR tags LIKE ''{$selectedtag}' %'
      OR tags LIKE '% '{$selectedtag}''

Answer 7

You could use MySQL's regular expressions.

SELECT * FROM content WHERE tags REGEXP '(^| )selectedtag($| )'

Be aware, though, that the use of regular expressions adds an overhead and might perform poorly in some circumstances.

Another simple way, if you can alter your database data, is to ensure that there is an empty space before the first tag and after the last one; A little like: " elephant animal ". That way you can use wildcards.

SELECT * FROM content WHERE tags LIKE '% selectedtag %'