Ad

DB Query Difference Between Turkish Letters - Octobercms

- 1 answer

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.

Ad

Answer

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.

Ad
source: stackoverflow.com
Ad