Disabling MySQL queries for foreign accents

Question

I have to admit that I don’t know php and that my current script has been inherited ...

It queries the MySQL database with the name cityand returns all instances found in this city.

I had several problems: the first thing to do with hyphens (for example, Stratford-upon-Avon); what was decided with the addition

$searchq = str_replace( '-', ' ', $searchq );

which allows me to enter data into the database without a hyphen.

My remaining problem is related to a foreign accent (in particular: sharp, serious, envelope, cedilla, tilde). I tried a million functions, many of which I found on this site and cannot make it work.

the main php code of my current page is

$searchq = filter_var("%{$_POST['keyword']}%", FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH); // Sanitize the string

$ searchq = str_replace ('-', '', $ searchq);

$sql = "SELECT Image, Chain, Country, City, Top as '', Medium as '', Low as '' FROM Chains WHERE Country LIKE ? OR City LIKE ?"; // Your query string

$prepare = $mysqli->prepare($sql); // Prepare your query string
$prepare->bind_param('ss', $searchq, $searchq); // Bind the placeholders to your search variables
// s = string | i = integer | d = double | b = blob
$prepare->execute(); // Execute the prepared statement
$prepare->store_result(); // Store the results for later checking

, , , ...

- , ,

?

, , script

EDIT SQL

ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

, ...

# 1253 - COLLATION 'utf8_general_ci' CHARACTER SET 'utf8mb4'

Answer 1

MySQL .

, :

select _utf8'résumé' COLLATE utf8_general_ci = _utf8'resume'

, utf8mb4,

select _utf8mb4'résumé' COLLATE utf8mb4_general_ci = _utf8mb4'resume'

, résumé resume . .

, unicode. case_insensitive . e- .

?

• , - (City, Country) utf8 , , utf8mb4.

• , , .

• . . , WHERE City = 'Sèvres' WHERE City = 'sevres' . , Google.

, , - .

CREATE TABLE chains_backup SELECT * FROM chains

.

  alter table chains
       modify City  varchar(255)
                    character set utf8mb4
                    collate utf8mb4_general_ci

varchar(255) . , , .

. , , .

, ñ . ñ n . ñ - . , , utf8_spanish_ci utf8mb4_spanish_ci.

, , : just_clean , .

WHERE City LIKE 'stratford%', WHERE City = 'stratford' - . LIKE Stratford-upon-Avon, Stratfordshire.