problem with searching case sensitive database entries

[izzy]

Hi,

 

My search query will not return any records when i search for words that are entered into my database with a capital letter.

The problem seems to be that i am searching in 3 different database fields and only one field returns records containing a capital letter.

here is the an example of the code:

$qeury = SELECT * FROM table WHERE LOWER(field_1) OR LOWER(field_2) OR LOWER(field_3) LIKE '%$search%' ORDER BY date

The search works well on field_1.

When i enter a search that i know is in one of the other fields and contains a capital letter then there is no match.

field_2 and field_3 are not converted to lower-case for some reason.

 

Your help will be greatly appreciated!

[QuickOldCar]

Instead of lowering the query terms you could make your database collation *_ci which is case insensitive

utf8_unicode_ci is a good choice

 

Your multiple LIKE queries should be set up like the following

SELECT * FROM table WHERE (LOWER(field_1) LIKE '%$search%' OR LOWER(field_2) LIKE '%$search%' OR LOWER(field_3) LIKE '%$search%') ORDER BY date

Personally I prefer to use mysql fulltext in boolean mode for searches

https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

 

Create a fulltext index and a query would be similar to:

SELECT * FROM `table` WHERE MATCH (field_1,field_2,field_3) AGAINST ('$search' IN BOOLEAN MODE) GROUP BY date

[izzy]

Unfortunately this did not solve the problem.

 

I already had a *_ci collation set for the database.

I could not succeed in setting a lowercase command for the full text example you gave me. (that's all me.. i know :-( but still it didn't solve the problem)

 

In the first example i have the exact same outcome as before.

The text in the first field returns the right records.

Yet the other fields don't return records when searching for a word that contains a captital letter.

[oh_php_designer]

Izzy,

 

Can you provide a example of what your searching for and also provide an example of what your data looks like?

[Barand]

Did you try the correct syntax that QuickOldCar suggested?

SELECT * FROM sample;
+-----------+--------+--------+--------+
| sample_id | cola   | colb   | colc   |
+-----------+--------+--------+--------+
|         1 | aabbcc | ddeeff | gghhii | <- match
|         2 | ppqqrr | BBccdd | xxyyzz | <-
|         3 | abcabc | defdef | hijhij |
|         4 | zzzyyy | kkklll | ccaabB | <-
|         5 | aaaaaa | cccccc | xxxxxx |
+-----------+--------+--------+--------+

SELECT cola, colb, colc
FROM sample
WHERE 
    (cola LIKE '%bb%')
 OR (colb LIKE '%bb%')
 OR (colc LIKE '%bb%');

+--------+--------+--------+
| cola   | colb   | colc   |
+--------+--------+--------+
| aabbcc | ddeeff | gghhii |
| ppqqrr | BBccdd | xxyyzz |
| zzzyyy | kkklll | ccaabB |
+--------+--------+--------+

[Tom10]

You could include BINARY in the statement example:

$qeury = SELECT * FROM table WHERE BINARY LOWER(field_1) OR BINARY LOWER(field_2) OR BINARY LOWER(field_3) LIKE '%$search%' ORDER BY date

[izzy]

The problem was not the query but it was the database fields.

For some reason these fields were set as BLOB. When i converted them to TEXT the problem was solved.

BLOB fields can not be case-insensitive.

 

I thank you all for your help.

 

Problem solved :-)