php - Ignore accented chars Eloquent query

652

I'm trying to query registers with fields that can contain accents likeà á ä (Catalan language). When I search 'alex' it does not returns 'Àlex' register.

I'm usinglaravel 5.5,mysql 5.7 andPHP 7.0.

Search code:

$client = new Client();
$client = $client->newQuery();

if ($request->has('name') && !empty($name = $request->input('name'))) 
{
    $client->where('name', 'LIKE', "%$name%");
}

return $client->get();

How can I query registers without distinguishing accents and case of the chars using Eloquent and Laravel?

Answer

Solution:

The problem was the configuration of test. I was using sqlite instead of mysql. Thanks for the answers. The default configuration of laravel for mysql works:

config/database.php

'mysql' => [
    ...
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    ...
]

630

Answer

Solution:

To have an accent insensitive search you can change thecollation at runtime in the raw sql query,

Try:

$client->whereRaw("name like  '%$name%' collate utf8_general_ci ");

836

Answer

Solution:

SHOW CREATE TABLE. You will probably find thatCOLLATION ..._bin. Change "bin" to "general_ci" so that accent stripping and case folding will be done during searching and sorting.

People are also looking for solutions to the problem: javascript - Fail to call last if condition

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question