How can i search strange characters in Laravel?

user_8688095 0
Viewed 1547

I have a problem with the search on my site. I have a search input to find products by name. If I type 'leña' (the problem is the strange characters).

This is in the controller

 $termino = $_POST['search']);
 $locale = LaravelLocalization::getCurrentLocale(); //es in this case
 $products = Product::where('title', 'LIKE', "%\"{$locale}\":%{$termino}%");

This is the content of the field 'title' in the database.

{"es":"PAN DE LE\u00d1A"}

The search returns 0 results.

I am sure the problem is the codification and I don´t know how to resolve it.

The codification is utf8mb4.

Thanks

laravel encode mysql php
2 Answers

I would simply encode the string before performing the query, e.g:

// assuming that $termino is a string 
// json_encode should return the unicode representation
$search = json_encode($termino);

// I changed the where condition a bit, 
// but your own condition is also do fine
$products = Product::where('title->'.$locale, 'LIKE', '%'.$search.'%');
user_28341200

The problem does not really lie on non-English characters. The root issue is that you store a complex data format (JSON) in a single database cell, as opposed to designing your database to hold the individual pieces of data:

create table product_title (
    product_id int(10) not null,
    locale varchar(2) not null,
    title varchar(200),
    primary key (product_id, locale)
);
insert into product_title (product_id, locale, title) values (1, 'es', 'Leña');
insert into product_title (product_id, locale, title) values (1, 'en', 'Wood');

You have a relational database but you haven't designed it around your data structure, so you cannot use most of its features, inclusing basic stuff like WHERE or ORDER BY.

You can fix the design (please, do) or apply a workaround:

  • If you have a recent MySQL version, you can make the column of JSON type.

  • You can create two versions of your JSON data and expect that database values do not have mixed cases:

    $locale = 'es';
$termino = 'Leña';
$data = [
    $locale => ":%{$termino}%"
];
$ascii = json_encode($data);
$unicode = json_encode($data, JSON_UNESCAPED_UNICODE);

// Use your framework's syntax instead:
$sql = 'SELECT * FROM product WHERE title IN (:ascii, :unicode)';
$params = [$ascii, $unicode];

Please note there's no sane workaround to use LIKE wildcards too.

user_135080
Related