PHP convert string variable that comes as ASCII to UTF8_unicode_ci keeping national characters

Question

In Laravel I have to migrate data from one database on MySql to the other database. Data from first MySQl comes as ASCII and have national characters. And I have to save this data in the second MySql database. Second database have column in UTF8_unicode_ci encoding. Code to get data from first database:

$pdo = DB::connection('mysqlPDO')->getPdo();
        
        $stmt = $pdo->prepare("SELECT * FROM `form_data` WHERE sent_to_pbox = 0 AND LIMIT 1");
        $stmt->execute();
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $stmt->closeCursor();
        unset($stmt);

And code to save in the second database:

$clientAddressEntity = ClientAddress::create([
                        'client_id' => $clientEntity->id,
                        'street' => mb_convert_encoding($importDTO->street, 'ASCII', 'UTF-8'),
                        'building_no' => utf8_encode($importDTO->building_no),
                        'post_code' => utf8_encode($importDTO->post_code),
                        'city' => utf8_encode($importDTO->city),
                        'country_id' => isset($countryEntity) ? $countryEntity->id : null,
                    ]);

mb_convert_encoding and utf8_encode doesn't work. Polish letters like ł,ę,ą and etc are saved in database as ? Table in the second database looks like:

CREATE TABLE `client_address` (
  `id` int NOT NULL,
  `client_id` int DEFAULT NULL,
  `street` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `building_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `post_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_id` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

When I add:

'street' => $importDTO->street . ' ' . mb_detect_encoding($importDTO->street),

I get result: Ja?minowa ASCII

First of all remove `utf8_encode()`. It's likely the cause of your problems — Dharman, Jun 01 '21 at 16:57
I think you meant the old deprecated `utf8` charset instead of ASCII. Please confirm from which charset you are migrating truly — Dharman, Jun 01 '21 at 17:04
Also see the database-specific steps [here](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) as well. Ensure the character set on your connection to both servers matches what the database is using. — miken32, Jun 01 '21 at 17:06
When I removed `utf8_encode()` data inserted into database looks like that: Os. S?owia?skie — Jonson, Jun 01 '21 at 21:59
How can data both come as ASCII **and** have national characters. The ASCII standard does not include any national characters so I don't think what you're getting is actually ASCII so you should first figure out what encoding you're actually getting and then convert it accordingly (if needed, most times the conversion can be done for you via the MySQL connection) — apokryfos, Jun 02 '21 at 06:11

score 0 · Accepted Answer · answered Jun 01 '21 at 22:34

0

This is solution, in controller I have to add set utf8:

  $pdo = DB::connection('mysqlPDO')->getPdo();
  $pdo->exec("set names utf8");
  $stmt = $pdo->prepare("SELECT * FROM .... );

answered Jun 01 '21 at 22:34

Jonson

53
1
8

PHP convert string variable that comes as ASCII to UTF8_unicode_ci keeping national characters

1 Answers1