0

I am searching a database for names of schools. One of the schools around here is named E.O. Green. The issue I'm having, is if someone types "E.O. Green" they naturally get the school in the list of results. However if a someone types "EO Green" it is not displayed, but it should be.

Here is the query:

SELECT DISTINCT name 
FROM donations 
WHERE name LIKE '%$query%' 
ORDER BY CASE WHEN name LIKE '$query%' THEN 1 
          WHEN name LIKE '%$query' THEN 3 
          ELSE 2 END 
LIMIT 8

Is there a solution to this that I am just overlooking?

This is not a question about stripping punctuation in PHP, this is a question about ignoring punctuation in the database in a MySQL query.

[EDIT] The solution apparently is to do a replace on the MySQL Query

WHERE REPLACE($field, '.', '') LIKE '%$query%'

and then also strip punctuation from the users input

$query = str_replace(".", "", $query);

which accomplishes what I'm after, but I'm still curious if there is a simpler way. Although I will call this solved for now, as this does the job.

Fred Langemark
  • 130
  • 14
  • Possible duplicate of [how to strip punctuation in php](https://stackoverflow.com/questions/5233734/how-to-strip-punctuation-in-php) – Simon Nov 29 '18 at 00:52
  • write a regex to strip user input for punctuation,spaces and convert string to lowercase then compare. – Mike Ross Nov 29 '18 at 01:05
  • The punctuation is not in the user input, it is in the database. I need it to match that database entry regardless of whether or not the user includes the punctuation. – Fred Langemark Nov 29 '18 at 01:06
  • 1
    https://stackoverflow.com/a/9850434 – esdebon Nov 29 '18 at 01:21
  • Thank you esdebon. This works, but only if the user doesn't include the punctuation. So basically, I could do this, and then strip the punctuation from the users input, and that should do the trick. – Fred Langemark Nov 29 '18 at 01:25

1 Answers1

0

Depending on the version of MySQL you are using the solution to your problem could be to use full text search.

There is a natural language parser that will cater for searches containing delimiters (e.g. comma, space and period). Make sure to check your MySQL version as some older versions don't support full text search for InnoDB.

cherrysoft
  • 1,165
  • 8
  • 17