mysql full-text search

Question

Have a issue going on with mysql queries. I use full-text search.

I try to find exact match in my photo table, but problems occur.

Let us say I have a photo description like "Reodor Hansen have trouble with mysql"

IF I then search for a user named Reodor Mysql, the picture where Reodor Hansen is tagged in, will be visible.

Is there options to get a exact match? I now about quotes like ' ' and " " but they are not helping me here.

Problem is that I don't wont Reodor Hansens photos visible in search when for Reodor Mysql.

Here is the query I use

SELECT * FROM _photos WHERE MATCH (description) AGAINST ( ' Reodor Mysql ' IN BOOLEAN MODE)

Further. Any idea how I can take this a step further? So I in the search box can write

"all pictures of Reodor Mysql" or "show me the last 10 pictures Reodor Mysql uploaded"

and the search respond on this?

You can use (my) php library for this: github.com/alexschwarz89/easy-mysqli-fulltext — Alex2php

alexrom7 alexrom7 · Accepted Answer · 2013-04-04T14:30:59

If you want to find an exact phrase, you could use double quotes. Just like this

SELECT * FROM _photos WHERE MATCH (description) 
                  AGAINST ( '"Reodor Mysql"' IN BOOLEAN MODE);

If you just want the last 10 pictures you could add a LIMIT to your query. Something like this

SELECT * FROM _photos WHERE MATCH (description) 
                  AGAINST ( '"Reodor Mysql"' IN BOOLEAN MODE) 
                  ORDER BY uploaded_date DESC 
                  LIMIT 10;

mysql full-text search

2 Answers