PHP/MySQL - include plurals but exclude singulars

I am building a site with a requirement to include plural words but exclude singlular words, as well as include longer phrases but exclude shorter phrases found within it.

For example:

• a search for "Breads" should return results with 'breads' within it, but not 'bread' or 'read'.

• a search for "Paperback book" should return results with 'paperback book' within it, but not 'paperback' or 'book'.

The query I have tried is:

SELECT * FROM table WHERE (field LIKE '%breads%') AND (field NOT LIKE '%bread%')

...which clearly returned no results, even though there are records with 'breads' and 'bread' in it.

I understand why this query is failing (I'm telling it to both include and exclude the same strings) but I cannot think of the correct logic to appl开发者_StackOverflowy to the code to get it working.

---

Searching for %breads% would NEVER return bread or read, as the 's' is a required character for the match. So just eliminate the and clause:

SELECT ... WHERE (field LIKE '%breads%')
SELECT ... WHERE (field LIKE '%paperback book%');

---

You should consider using FULL TEXT SEARCH.

This will solve your Bread/read issue.

I believe use of wildcards here isn't useful. Lets say you are using '%read%', now this would also return bread, breads etc, which is why I recommended Full Text Search

---

With MySQL you can use REGEXP instead of like which would give you better control over your query...

SELECT * FROM table WHERE field REGEXP '\s+read\s+'

That would at least enforce word boundaries around your query and gives you much better control over your matching - with the downside of a performance hit though.