Search code examples
mysqlsearchcase-insensitive

MySQL query BOOLEAN MODE Case Sensitivity

My search is case sensitive, but I want it to be case insensitive. In MySQL I have my database and table set to utf8_general_ci. The search is still case sensitive. I have been doing some research and it seems the reason behind this is BOOLEAN MODE in my query. Is there a way to make it case insensitive? so no matter how I type any word with any sensitivity it will always bring it up in the search result?

SELECT 
        s_cost_sheet.partnumber,
        s_cost_sheet.description,
        s_cost_sheet.price,
        s_cost_sheet.notes

    FROM s_cost_sheet
    WHERE MATCH ( partnumber, description, price, notes ) 
    AGAINST('%".$search."%' IN BOOLEAN MODE) ORDER BY partnumber, description, price, notes ASC";

I have tested the search in phpMyAdmin and it works no matter how a type the word plate, it can be Plate, PLATE, plaTE. It all works fine, so it must be something within this that is causing the issue.

Solution

  • If one of the columns is of type int or numeric then the search becomes case sensitive. Also there's no need to use % in the searched string