DB Query Difference Between Turkish Letters - Octobercms
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.
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.
Related Questions
- → "failed to open stream" error when executing "migrate:make"
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS create a multi select Form field
- → October CMS - Conditionally Load a Different Page
- → How to disable assets combining on development in OctoberCMS
- → October CMS - Radio Button Ajax Click Twice in a Row Causes Content to disappear
- → OctoberCms component: How to display all ID(items) instead of sorting only one ID?
- → In OctoberCMS how do you find the hint path?
- → How to register middlewares in OctoberCMS plugin?