I am trying to implement a search engine on my site, which all content is stored in mysql. I am having troubles getting the queries to return anything but the exact matches, which I know is an issue. How can I get around that ?
If someone does a search on "Help", I use queries with "like" statements that will only return instances of "Help", but not "help" or "HELP", etc... I would like to return these in the results while not changing the way the data is stored in mysql (Had someone recommend I store all data as lowercase, then I just have to search the lowercase...)
Thanks for any help..
So you want a case-sensitive search? Or are you trying to eliminate the case-sensitive string?
Searches using the LIKE operator are case-insensitive by default (so if you did something like:
SELECT * FROM TABLE_NAME WHERE FIELD_NAME LIKE 'a%'
it would return anything starting with a or A).
If that's not what you're experiencing, it may be that you're using an old version of MySQL, which had searches performed case-sensitive by default. If this is the case, either upgrade to a later version or use some kind of workaround; this might be something along the lines of:
- this converts both the string you're comparing and the database value to uppercase, meaning that hElP would match helP etc.
$strSQL = "SELECT * FROM TABLE_NAME WHERE UPPER(FIELD_NAME) LIKE '" . strtoupper($comparison_value)
Hope that hElPs ;).
Thanks, I think that is what I am looking for... I'll give it a try...