[RESOLVED] Case Insensitive LIKE Query no longer works.

HairyArse

Hey guys,

I know there are a ton of posts about case insensitive queries and I've done my research, but can't find an answer to the specific problem I have which is as follows:

My search page has sudddenly started yielding unpredictable results.

My website is here.

Basically I want users to be able to search for articles based on the text in an article's headline. This needs to be case insensitive as most of the time, the first letter in each word in the headline is capitalised.

(The search box is in the top left hand corner under the logo.)

Another reason for case insensitivity is that the different people who write for the website write thing differently, so for example, users might want to search for GTA, Gta, or gta. And I need all results to be displayed regardless of the search criteria.

Here's where the problem is.

This used to work fine with the following:

$query = "SELECT * FROM tbl_articles WHERE LCASE(headline) LIKE LCASE('%$search_text%');";

However all of a sudden, this is no longer case insensitive.

I have tried using UPPER instead of LCASE and that doesn't work either.

I'm really confused as this used to work fine.

laserlight

Did your host upgrade the database software? Oh, and what relational database management system are you using anyway? (Please include version number.)

HairyArse

I'm using MySQL 4.1.22-standard on a LINUX based server at DataFlame.

NogDog

What is the column type for 'headline'?

HairyArse

The field headline is a blob.

In hindsight it should probably be a varchar and that might be part of the problem though it seems unlikely as I also have a field called article_text that is a blob and this same query worked fine on that in the past.

NogDog

Yep, if you look at the manual entry for LCASE()/LOWER(), you'll see that it does not work on binary types. If you have a reason for it to be a BLOB, then you'll need to cast it to a string type before applying the LCASE(). However, since it seems to be textual content, it would probably be better to change it to a TEXT or VARCHAR type.

HairyArse

How bizarre. I changed the field type to a varchar(250) and it now works a treat again.

I dunno how the field type got changed but it works now and that's all that counts!

Thanks guys.