Case Sensitive searching

[dmajwool]

Hello,

I would like to make the DaDaBiK searches to be case insensitive. At the moment all my searches are case sensitive.

I understand that there may be some control over this done by the mysql server, so I did the following.

I switched on $display_sql = 1; in config.php and ran a search in DaDaBiK.

the relevant part of the DaDaBiK sql was:-

WHERE `phpbb_users`.`username` LIKE '%david%'

DaDaBiK returns just the one username that contains "david_xxx"

If I change the DaDaBiK search and search for :-

WHERE `phpbb_users`.`username` LIKE '%David%'

I see a dozen or so results with the capitalised "DavidXxx"

If I switch to phpMyAdmin on the same database table and run the following sql:-

SELECT * FROM `phpbb_users` WHERE `phpbb_users`.`username` LIKE "%david%"

phpMyAdmin returns many records. As well as the "david_xxx" one, I see a dozen or so "DavidXxx", "DavidYyy" and so on.

So it seems like the DaDaBiK search is case sensitive where the phpMyAdmin search is not case sensitive.

I would like DaDaBiK to return the dozen or so mixed case records with a non case sensitive search.

Many thanks for your advice,
David.

 

[dmajwool]

DaDaBIK version 9.2-Monterosso pro,

PHP Version: 5.6.37

mysql version: 5.6.21

Web server: Apache

 

[eugenio]

Hello,
it's very strange, the result in phpmyadmin and dadabik should be the same, there isn't any additional check in DaDaBIK to make the query case sensitive; can you double check it and can you check (in phpmyadmin) which is the collation of the field username ?

best,

 

[Mbeauc636]

I had this same issue on some fields that were set to collate UTF8_BIN. I altered them to UTF8_GENERAL_CI for case-insensitve.

ALTER TABLE 'tablename'
MODIFY COLUMN columname VARCHAR(255) SET UTF8 COLLATE utf8_general_ci

Searches now work and are not case sensitive. My tables are static so dadabik is just used for a front-end for me so I am not sure on side effects of performance for doing this if any. Google search utf8_bin vs utf8_general_ci for more information on this.

 

[eugenio]

I confirm that UTF8_BIN provides case sensitive comparison, but if you should get the same result with phpmyadmin or any other sql client.

Best,