Match and replace with substr_replace/preg_match, then search with REGEXP
I'm currently looking for an answer to a php/mysql search function where I want to add a special character to the initial search using PHP substr_replace and preg_match, then look for this new variable in the SQL database using REGEXP.
Here is how it works. When someone types in just the letter "a", I want to add a smart quote to the letter so that the database will search for a’
Preg match identifies the character "a", and substr_replace adds the smart quote at the end ’
Now at the moment when I output this new variable before it is searched using REGEXP, the browser shows the new variable with the smart quote just as I want it to be searched (so therefore I think that preg_match and substr_replace have been working properly). I have entered a smart quote directly into the mysql database using ALT + 0146 (the database is of type InnoDB and collation latin_swedish_ci).
However, when I search the database using REGEXP, well there are no returned results.
I was thinking this might either be a problem with how I am adding the smart quotes to the initial variable, or that it might be a problem with the SQL database type or collation. The field that I am searching is TEXT.