DaDaBIK
This forum is devoted to the discussion about the software DaDaBIK database front-end
the PHP Database application generator for MySQL, PostgreSQL and SQLite; also available for Wordpress.

Welcome! Log In Create A New Profile

Advanced

Case Sensitive searching

Posted by dmajwool 
Case Sensitive searching
December 22, 2018 10:37AM
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.
Re: Case Sensitive searching
December 22, 2018 10:46AM
DaDaBIK version 9.2-Monterosso pro,

PHP Version: 5.6.37

mysql version: 5.6.21

Web server: Apache
Re: Case Sensitive searching
December 24, 2018 09:03AM
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,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Re: Case Sensitive searching
January 06, 2019 07:45AM
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.



Edited 1 time(s). Last edit at 01/06/2019 07:46AM by Mbeauc636.
Re: Case Sensitive searching
January 07, 2019 07:03AM
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,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Sorry, only registered users may post in this forum.

Click here to login