PHP and MySQL Character Encoding Problems: Diagnosing utf8mb4

If a name such as “Çağrı” appears as garbled symbols, do not immediately run ALTER TABLE across the database. The value may have been stored incorrectly, or a correct value may be misinterpreted by the PHP connection or browser. Similar-looking symptoms can require very different repairs.

Start with one record. Compare its application display, stored bytes, and the behavior of a newly created test value. Old records failing while new records work suggests a historical import problem. If only one PHP page is affected, inspect that page's connection and response settings first.

Separate four encoding layers

• The PHP file, CSV, or SQL dump has an actual byte encoding.
• The connection tells MySQL how to interpret exchanged bytes.
• The destination column has its own character set.
• The HTTP response tells the browser how to decode the output.

Seeing UTF-8 configured at one layer does not fix the others. An HTML meta tag cannot recover letters previously replaced with question marks. Changing a database default also does not automatically convert all existing columns.

Check bytes rather than trusting the display

Run this through a correctly configured UTF-8 client or UTF-8 file. “Çığ” contains three characters represented by six UTF-8 bytes:

SET NAMES utf8mb4;
SELECT HEX('Çığ') AS bytes_hex,
       CHAR_LENGTH('Çığ') AS characters,
       LENGTH('Çığ') AS bytes;

Expected values are C387C4B1C49F, 3, and 6. Different character and byte counts are normal. Adapt this example to inspect an actual stored value:

SELECT id, name, HEX(name), CHAR_LENGTH(name), LENGTH(name)
FROM customers
WHERE id = 42;

If the bytes are correct but one application displays them incorrectly, concentrate on reading and presentation. If stored bytes are wrong, fixing the connection may prevent future damage without repairing old rows. Avoid exposing personal data through public screenshots or diagnostic pages.

Inspect the application's own connection

phpMyAdmin creates its own connection; its session settings do not establish the application's PDO settings. Run this query through the affected connection:

SELECT @@character_set_client,
       @@character_set_connection,
       @@character_set_results;
SHOW FULL COLUMNS FROM customers;

Specify the character set in the PDO DSN. The following test assumes credentials are provided securely through environment variables. Save the PHP file as UTF-8. In production, log connection errors privately and display a generic error page rather than exception details.

<?php
declare(strict_types=1);
header('Content-Type: text/html; charset=UTF-8');
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=application;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
     PDO::ATTR_EMULATE_PREPARES => false]
);
$stmt = $pdo->prepare('SELECT ? AS text_value');
$stmt->execute(['Çığ, İstanbul, şeker']);
$text = $stmt->fetchColumn();
echo htmlspecialchars($text, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

This tests a connection round trip without writing a table. It does not prove that all application columns are configured correctly. Prepared statements transport values safely but are not an encoding repair mechanism.

Test storage separately

This temporary table lasts only for the current session and requires temporary-table privileges. Run it in a test database:

SET NAMES utf8mb4;
CREATE TEMPORARY TABLE encoding_test (
  id INT PRIMARY KEY,
  text_value VARCHAR(100) CHARACTER SET utf8mb4
) ENGINE=InnoDB;
INSERT INTO encoding_test VALUES (1, 'Çığ'), (2, '🐘');
SELECT id, text_value, HEX(text_value),
       CHAR_LENGTH(text_value), LENGTH(text_value)
FROM encoding_test ORDER BY id;

The emoji row should have one character and four bytes. utf8mb3 cannot represent it. If the server rejects a collation name instead, use the separate collation compatibility checks.

Do not bulk-convert damaged history blindly

ALTER TABLE with CONVERT TO CHARACTER SET interprets existing values according to their declared character set. Mislabeled bytes can violate that assumption. One conversion may repair some rows while corrupting rows that were already correct. Back up the data, classify affected samples, and compare expected bytes in a small copy first.

Applying another decode operation to every output has the same risk: double-encoded text is not the same as genuine Latin-1 text. If the original letters were permanently replaced with question marks, the stored bytes may no longer contain enough information to recover them; use a sound backup or source data.

Finally, send the same test text through creation, editing, searching, export, and reimport. Preserving bytes throughout the path is stronger evidence than making a single page look correct.

References: PDO_MYSQL DSN, connection character sets, and column conversion.