DB query difference between Turkish Letters - Octobercms

Question

I'm using following configuration for Database

'mysql' => [
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'port'      => '3306',
            'database'  => 'databsename', 
            'username'  => 'root',
            'password'  => 'root',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ],

As you can see, I am using 'collation' => 'utf8_unicode_ci' and 'charset' => 'utf8'. The problem I'm having is with querying Turkish characters. For example, It treats English G and Turkish Ğ same also goes same with other Turkish characters.

See this mysql example

mysql> select * from yeoman_wordbank_ where word_tr like "ğ%";
+----+-----------+--------------------------+------------+---------------------+---------------------+---------------------+--------------+----------------+----------------+----------------+
| id | word_tr   | word_gr                  | pubordraft | created_at          | updated_at          | deleted_at          | word_image   | description_en | description_tr | description_gr |
+----+-----------+--------------------------+------------+---------------------+---------------------+---------------------+--------------+----------------+----------------+----------------+
| 22 | gurbet    | γρουμπέτι                |          0 | 2017-06-08 06:25:19 | 2017-06-23 11:39:40 | 2017-06-23 11:39:40 | /image19.jpg |                |                |                |
| 23 | gurbetçe  | γκρουμπέτικα             |          0 | 2017-06-08 06:26:19 | 2017-06-23 11:39:40 | 2017-06-23 11:39:40 | /image20.jpg |                |                |                |
| 32 | Gancelli  | Καντζέλλιν               |          1 | 2017-07-12 16:31:40 | 2017-07-12 16:31:40 | NULL                |              |                |                |                |
| 33 | Gabira    | Καπύρα                   |          1 | 2017-07-12 16:32:37 | 2017-07-12 16:32:37 | NULL                |              |                |                |                |
+----+-----------+--------------------------+------------+---------------------+---------------------+---------------------+--------------+----------------+----------------+----------------+
4 rows in set (0.00 sec)

As you can see it gave me results of normal English G not the one with Turkish ğ

PS: I was using this to query

Word::where( $WordDB_Field, 'LIKE', $URL .'%' )->get()

But it seems me to bring no luck. I tried utf8_decode() as well on $URL which is basically passing the letters.

Any help would be really appreciated.

It is a collation problem indeed. Please have look here: https://stackoverflow.com/questions/2607130/mysql-treats-%C3%85%C3%84%C3%96-as-aao Knowing, the question refers to german umlaute, but maybe it's a helpful hint - especially the links of mysql documentation. — BenRoob, Jul 12 '17 at 14:27
it is indeed! i am struggling badly with the collation, i can't change now :/ laravel is just not doing on fly with query as well. Although the solution is this mysql> select * from yeoman_wordbank_ where word_tr like "ğ%" collate utf8_bin; but unable to code. tried in DB:raw or whatnot. it says this "SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_bin' is not valid for CHARACTER SET 'binary' (SQL: select * from `yeoman_wordbank_` where LOWER(word_tr) LIKE ğ% collate utf8_bin)"). — Danish, Jul 13 '17 at 09:26
finally solved it thank you very much for sharing that link!!! — Danish, Jul 13 '17 at 10:08

score 1 · Accepted Answer · answered Jul 13 '17 at 10:12

so the answer was basically to use utf8_bin collation but i had my Database in production site already and it was huge problem. the sql solution found from @BenRoob was working only in SQL but Laravel was headache.

So, the SQL solution was this

mysql> select word_tr from yeoman_wordbank_ where LCASE(word_tr) like "ğ%"  COLLATE utf8_bin;

the laravel one was this

$query = DB::select( DB::raw( "select * from yeoman_wordbank_ where LOWER($WordDB_Field) like '$URL%' collate utf8_bin" ) );

the above laravel query was the only one which worked, i tried many combinations.

score 0 · Answer 2 · answered Jul 12 '17 at 21:41

0

Did you checked your table collation?

answered Jul 12 '17 at 21:41

Eliran Givoni

144
1
9

DB query difference between Turkish Letters - Octobercms

2 Answers2