Coherent UTF-8 data manipulation from MySQL to JSON in PHP

This short guide may help you to quickly create a simple data flow from the query to the JSON response for example if you want to build an API endpoint. You can find here some details that can save you time and answer to the following question: “Why my accented chars and emojis are not displayed anymore?”.

This may happen if you upgrade MySQL and many chars are not working anymore in your web application. This problem could be easily solved editing the /etc/mysql/my.cnf (Ubuntu) file adding the following configuration rows to restore the UTF8 charset by default:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

Remember then to restart the MySQL daemon:

 $ sudo service mysqld restart

If you don’t have access to this file here is a specific focus on how to deal in particular with accented characters and other special chars.

Before to start, backup your tables/database. Safety first.

Then connect to PhpMyAdmin and set the collation of the involved tables to utf8mb4_unicode_ci (change also all columns collations).

Build your PHP script (version 8.1) in such a way:

<?php
	// Connection to db and charset
	$conn = get_connection();
	$conn->query("SET NAMES 'utf8'");
	$conn->query("SET CHARACTER SET 'utf8'");

	// Query execution
	$ssql = $conn->query("SELECT * FROM <mytable>");
	$data = $ssql->fetch_all(MYSQLI_ASSOC);
	$conn->close();

	// Headers for JSON/UTF-8 encoding
	$status = 200;
	header("HTTP/2 " . $status);
	header("Content-Type: application/json; charset=UTF-8");

	// Setup of the JSON response
	$response["status"] = $status;
	$status_message = "Something here";
	$response["status_message"] = $status_message;
	array_walk_recursive(
		$data,
		function (&$entry) {
			$entry = mb_convert_encoding($entry,"UTF-8");
		}
	);
	$response["data"] = $data;
	$json_response = json_encode($response);

	// Output response
	echo $json_response;

	// Function to get PDO connection
	function get_connection(){
		$servername = "localhost";
		$username = "<myusername>";
		$password = "<mypassword>";
		$db = "<mydb>";
		$conn = new mysqli($servername, $username, $password, $db);
		if ($conn->connect_errno)
		    exit();
		else
		    return $conn;
	}
?>

You can also specify the charset in the html code if needed:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

---

This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.