I use PHP with MySQL to make a basic search engine on my website to search for books that people publish, but the problem is that I use something like this:
`title` LIKE '%".(string)$searchTerm."%'
NOTICE: the $ searchTerm variable is slipping out of hand.
I know this is widely used and works very well, but the problem is that if I have the title of a book, for example:
Link to Writer
The result will show if someone is looking for a "Writer Reference" (without double quotes). But if someone is only looking for the "Writers Reference", the result will not appear, so I am asked here what might be a good idea to solve this problem.
Using AJAX to query every time with every onkeyup event? Collect each output into an array? But this can slow down the process. Is there a way in MySQL that filters and ignores apostrophes, but at the same time, if someone uses the apostrophe, it will work? Regular expression operation?
Thank.
EDIT: I am not trying to prevent SQL Injections, I am looking for a way so that if someone searches for a word without an apostrophe, the result with an apostrophe appears, but at the moment with LIKE %% it will not be displayed, because it accepts direct words from the database . Therefore, if the search query does not have an apostrophe, and the result has one, it will not be displayed.
EDIT 2: As I followed the comments, I recently updated two columns in the database to use FULLTEXT via the ALTER TABLEdbname .bookADD FULLTEXT (title,description)
, , , , , MATCH(title,description) AGAINST('".(string)$sTerm."'), , , , , - : MATCH(title) AGAINST('".(string)$sTerm."'), , MATCH(title,title) AGAINST('".(string)$sTerm."') ( MATCH)
, - "Writes Reference" , - - , , php-?