mySQL search query : remove case sensitivity
I've had this situation before - a mySQL query using 'like' to search a string value for a given phrase sometimes becomes case sensitive when the query contains other 'Where' phrases. ( As far as I know, 'like' is supposed to be case INsensitive, and this is a known bug in some installations of mysql - I've tried updating mysql drivers and still it remains). But, regardless of the cause, there's an easy fix...
In this case I am working on a CartWeaver ecommerce site, and the simple product search in the admin is not behaving quite like I'd expect. Searching for "blue" does not return a product with "Blue" in the title (note the capital B), and vice-versa. To fix this, I found a simple method to force both the entered value and the query comparison string to be all-lowercase, which removes any issue of capital letters making a difference.
The original search code and resulting query go something like this
<cfif URL.find NEQ "">
<cfset QueryFind = URL.find>
</cfif>
( then the query )
SELECT [some fields]
FROM tbl_products
WHERE tbl_products.product_Name
LIKE '%#QueryFind#%'
My fix was to simply force the original search string to lowercase with ColdFusion
<cfif URL.find NEQ "">
<cfset QueryFind = lcase(URL.find)>
</cfif>
And then in the query, use mySQL's "lower()" function to do the same to the compared string value
SELECT [some fields]
FROM tbl_products
WHERE LOWER(tbl_products.product_Name)
LIKE '%#QueryFind#%'
Mind-blowing, really. I wish they'd fix this LIKE issue once and for all.