Character encoding issue with PHP/MySQL

Question

I have a web application I have been developing locally on my computer, I have recently launched it on my web server with the exact same settings for MySQL, but for some reason I am encountering a character encoding issue where "smart quotes" (’) are being displayed as black triangles with question marks (�), but only on the server.

This is on the HTML:

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

My local MySQL database (without character issues) is MySQL 5.7.26 with an InnoDB table with UTF8MB4 encoding and a table collation of utf8mb4-unicode-ci

My server MySQL database (with character issues) is MySQL 5.5.5-10.3.21 with an InnoDB table with UTF8MB4 encoding and a table collation of utf8mb4-unicode-ci

Perhaps there is a PHP setting somewhere I am missing?

Edit:

if I run this script on my computer is works perfectly, but if I run the same script on my server with the same data in the database, it give me the character encoding issue:

error_reporting(E_ALL);

include 'dbconfig.php';

$id = 19;

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_TO_STRING);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $sql = $pdo->prepare("SELECT * FROM products WHERE id=:id");
    $sql->execute(['id' => $id]); 

    var_dump ($sql->fetch());

    while ($row = $sql->fetch()) {
        $data[] = $row;
    }

    echo json_encode($data);

} catch(PDOException $e) {
    //echo "Error: " . $e->getMessage();
    echo "Database Error";
}
$conn = null;

All it takes, is one wrong charset setting in your application - everything needs to be the same charset! I have previously written an answer about UTF-8 encoding that contains a little checklist, that will cover most of the charset issues in a PHP/MySQL application. There's also a more in-depth topic, UTF-8 All the Way Through. Most likely, you'll find a solution in either one or both of these topics. — Qirel
– Qirel, Commented Oct 15, 2020 at 19:47
If the data is stored incorrectly in the database already - its already broken and its nearly impossible to fix. You need to insert the correct data with proper charset if that's the case, and fix the problems with the insert. — Qirel
– Qirel, Commented Oct 15, 2020 at 19:48
With your update: You don't set a charset for your PDO connection. See my first link for a specific example on how to do that (last part of the answer). Nor are there any PHP headers set. Those two are probably your curlpit. — Qirel
– Qirel, Commented Oct 15, 2020 at 20:14
Was a different version of PHP involved? (I don't know whether a different default in MySQL or PHP was involved in your particular problem.) — Rick James
– Rick James, Commented Oct 16, 2020 at 15:08

Rick James · Accepted Answer · 2020-10-16 15:05:11Z

1

(Since this clearly shows a PDO omission, I am reopening)

One-line change:

$pdo = new 
    PDO("mysql:host=$servername;dbname=$dbname;dbname=db;charset=utf8mb4'",
        $username, $password);

References:
http://mysql.rjweb.org/doc.php/charcoll#php
Trouble with UTF-8 characters; what I see is not what I stored (Look especially for "black diamond".)

answered Oct 16, 2020 at 15:05

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Character encoding issue with PHP/MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related