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;
Advertisement
Answer
(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”.)