problem with '-' and '&' string in mysql

Question

I have a search engine in PHP. When a search normally it's OK. Search text is 'company', and in the database there is 'company' in the field...

The problem is when the search text is &company or -company and the data is &company or -company there is no match. why?

problem with the - and & string...

Answer 1

Try putting your search terms in quotes. This should help mysql know you mean those characters literally in fulltext search:

SELECT * FROM tablename MATCH (company) AGAINST ('"&company"' IN BOOLEAN MODE)

SELECT * FROM tablename MATCH (company) AGAINST ('"-company"' IN BOOLEAN MODE).

Answer 2

If you are using FullText Search then the & and - are reserved characters. I could not find any nice solution to this problem. What I did is just remove the special character and run the full text search. For example if they are looking for At&t I run a search for "AT" "T", but if you have noise words At and A are in there and you will not get any results.

Another solution is to detect when they are requesting a special character and run a LIKE '%&Company%' search instead of a full text search, but this will affect the performance of the query.