0

An old page has the current setup:

  1. MySQL DB and tables: latin1_swedish_ci
  2. HTML: <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />

Everything works fine for years now.

Now I have downloaded the PHP files from FTP, updated the code and because my editor uses UTF8 I have issues with the presentation of the text from the PHP files.

If I change to <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> the text from HTML files is displayed properly but not the text from the database.

If I set the DB and tables to utf8_bin and import data via file_get_contents() > $mysqli->set_charset('utf8') > $mysqli->multi_query($sql_file), the text is presented correctly.

But the %LIKE% search does not work anymore?

I think UTF8 is more straightforward. What are the correct steps to solve this issue?

scriptPilot
  • 97
  • 7

1 Answers1

0

try to use utf8mb4_general_ci for DB tables and try to set correct charset for your DB connection. First SQL query to DB:

  $sql = "SET NAMES utf8mb4";

after that run your SQL query

Vladimir Miroshnichenko
  • 1,256
  • 10
  • 9
  • Thank you. With `mysql_set_charset('utf8')` I am able to write and retrieve correct data. Nevertheless, what is still broken is the `LIKE "%...%"` query. It does not work if there are special characters in the string. Example: `LIKE "%new%"` does not return the row with value `New México`. – scriptPilot Oct 25 '22 at 22:37
  • I figured out it work with `LIKE "%ew%"` but not with `LIKE "%new%"`. With ISO charset it works with both. Same with reg exp `.*new.*`: works with ISO, not with UTF8. `.*ew.*` works with both. – scriptPilot Oct 25 '22 at 22:44
  • I found the solution finally: utf8_bin is case-sensitive. So I choose a different collation utf8_gerneral_ci. – scriptPilot Oct 25 '22 at 22:52
  • Details: https://stackoverflow.com/questions/8083455/like-case-sensitive-in-mysql – scriptPilot Oct 25 '22 at 22:52