0

I have MySQL database table "PRICES" that is set SET NAMES utf8mb4; with colum name "TYPE" char(70) CHARACTER SET latin1 COLLATE latin1_swedish_ci

When I get the data in PHP 5.6.40 with Pear like this:

    $DSN = "$db_schema://$db_username:$db_password@$db_host/$db_database";
    $db = DB::connect($DSN);

    $db->setFetchMode(DB_FETCHMODE_ASSOC);
    $db->Query("SET NAMES utf8mb4");

    $prices = $db->getAll("SELECT * FROM prices");

Now I expect the column "TYPE" in the variable $prices["type"] to be some Cyrillic words but instead I get this Ñåìååí ñ äâå äåöà

I tried also:

$db->Query("SET NAMES utf8"); which get the same result as above

$db->Query("SET NAMES latin1"); which get this result => ������ � ��� ����

I tried to convert the last result with some php functions like utf8_encode() too, but things just get worst...

So, please, if somebody here knows where the issue is, I'm glad to hear your answers!

UPDATE

Here you can view the sql dump file for more details: https://pastebin.com/0pig4B1j

delyakov
  • 1
  • 4
  • I would start with `COLLATE utf8mb4_unicode_ci`. – KIKO Software Jun 29 '22 at 12:25
  • 1
    @KIKOSoftware generally you don't care for the COLLATE but rather for CHARACTER SET. Either way it could be a problem for the existing database. – Your Common Sense Jun 29 '22 at 12:28
  • Is the data in DB UTF8? Is it stored there correctly? If it isn't stored correctly you can't get it back. Similar to using black and white film and wanting color photo prints. – user3783243 Jun 29 '22 at 12:28
  • @user3783243 obviously not, otherwise SET NAMES latin1 would have worked. It's 1251. and you can get it back – Your Common Sense Jun 29 '22 at 12:29
  • @YourCommonSense The multibyte char(s) are not lost when written to ASCII column? – user3783243 Jun 29 '22 at 12:33
  • 1
    @user3783243 it's only the opposite: trying to write non-lower ASCII into multibyte column will result in ???? in database. But single-byte column will hold anything acting effectively as binary. – Your Common Sense Jun 29 '22 at 12:34
  • @user3783243 either way, currently the data is not multibyte, it's single-byte cp1251 (or so I bet, as there is little choice for Cyrillic). – Your Common Sense Jun 29 '22 at 12:44
  • here is the sql dump file: [link](https://pastebin.com/0pig4B1j) – delyakov Jun 29 '22 at 12:48
  • @markamber if you don't need to keep the existing data, simply configure the entire application to use utf-8. If you do, then you will need to convert your data the way described [here](https://www.percona.com/blog/2007/12/18/fixing-column-encoding-mess-in-mysql/) – Your Common Sense Jun 29 '22 at 15:47
  • The type is Семеен с две деца, right? – Your Common Sense Jun 29 '22 at 18:13
  • @YourCommonSense Thank you for the help mate and all of you guys! The problem in my case was that I don't have to UPDATE/CHANGE database. I only read from it and get the data. So the solution I found is to get the data in PHP with `SET NAMES latin1` because this is the charset of the table. After that I looped every row and used `$v["type"] = iconv("CP1251", "UTF-8", $v["type"]);` to convert Cyrillic to UTF-8. – delyakov Jun 30 '22 at 07:50

0 Answers0