Good time! There is such a problem - there is a site ( Link ) with a database that contains a lot of data in it. From the very beginning there was a mistake and the Cyrillic was recorded in the database as

Концентрат Ñывороточного протеина

But this view in Pma on the site itself all displayed correctly. But today, something on the hosting did what now and on the site became the same. Is there any way to somehow kerekodirovat data in the database itself that would translate these characters in Cyrillic - encoding of the site pages

 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

Encoding tables and databases utf8_general_ci type of tables InnoDB

How can you encode the data in the database in the correct form?

I add: judging by the picture here, these hieroglyphs went by the fact that the data was in utf-8 and the base was in utf-8 and the connection was in cp1252

  • Speech about MySql as I understand? add tag - 4per
  • Possible duplicate question: Setting the encoding in UTF-8 for the current connection with MySQL - androschuk
  • @ 4per yes true mysql site 2 years before today for everything worked fine - dantelol
  • @androschuk no, unfortunately, those inquires don't work - adding 3 lines to the connection $ db = mysql_connect ("*******", "******", "*****"); $ dd = mysql_select_db ("*******", $ db); mysql_query ("SET NAMES 'utf8'"); mysql_query ("SET CHARACTER SET 'utf8'"); mysql_query ("SET SESSION collation_connection = 'utf8_general_ci'"); change nothing - dantelol

2 answers 2

What about:

 mysql_query("SET NAMES 'utf8'"); mysql_query("SET CHARACTER SET 'utf8'"); mysql_query("SET SESSION collation_connection = 'utf8_general_ci'");

A source

  • Unfortunately I tried it does not change, so far the only option that I see is simply to recode all the data in the database from one encoding to another, but I have no idea how best to do it and ask for help \ - dantelol

In general, I solved the problem - I’ll place the deployed solution here; it may be useful to someone else, the fact was that the data were recorded in the default encoding of the connection to the server, namely mysql_query ("SET NAMES 'latin1'");

in the end, I simply exported the database from the host to the local host, and created a duplicate of it with completely cleaned (empty tables) and launched such a script

 define('BD_HOST','localhost'); //хост бд define('BD_PASS','password'); //пароль бд define('BD_LOGIN','dante'); //Логин бд define('BD_FROM','coa'); //исходная БД define('BD_IN','coreprot'); //конечная БД $tables = array ( 'bask', 'bonus', 'razdel', 'tovar'); //Массив со всеми таблицами которые надо переписать нормально foreach ($tables as $table) { mysql_connect(BD_HOST,BD_LOGIN,BD_PASS); mysql_select_db(BD_FROM); mysql_query("set names 'latin1'");//тут ставим дефолтную корявую кодировку (Это важно!!!) $query_select_from = "SELECT * FROM $table;"; $result = mysql_query($query_select_from); mysql_close(); //запросили все косячные данные и закрыли соединение mysql_connect(BD_HOST,BD_LOGIN,BD_PASS); mysql_select_db(BD_IN); mysql_query("set names utf8"); //открыли новое соединение, но уже указав нормальную кодировку while ($t = mysql_fetch_assoc($result)) { $values = ''; $f = false; foreach($t as $val) { $p = ($f)?',':''; $f = true; $add = (is_int($val))?"$val":"'$val'"; $values.= "$p $add"; }; $insert_in = "INSERT $table VALUES($values)"; mysql_query($insert_in); //запись прочтенной строки, уже в нормальном соединении }; mysql_close(); //закрываем соединение, что бы открыть его уже для другой таблицы.

};

As a result, the database was overwritten in the normal encoding and everything is fine in pma and on the site, immediately put the normal encoding when connecting, as follows:

 mysql_query("SET NAMES 'utf8'"); mysql_query("SET CHARACTER SET 'utf8'"); mysql_query("SET SESSION collation_connection = 'utf8_general_ci'");

and all the profit, the MAIN thing I repeat here is to guess the initial encoding of the connection in which everything was written over the joint