CODEWITHSUNDEEP
mysqlsearchcase-insensitive
asar
asar

Reputation: 108

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.

Upvotes: 5

Views: 1194

Answers (2)

Puerto AGP
Puerto AGP

Reputation: 423

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

Upvotes: 1

Ghazanfar Mir
Ghazanfar Mir

Reputation: 3543

TRY changing your table and column to utf8_general_ci and you don't have to use % wildcard with FULL TEXT Search

RECOMMENDATIONS Also avoid using a lot of columns with ORDER BY clause if you need quicker results.

Upvotes: 0

Related Questions