What is the best collation when using JSON and some special characters

Question

I'm trying to get data from mysql database with php and JSON and it's not working because of the collation I've chosen. I'm using these characters : ® ,é, è , ™, É My deafult collation in mysql is: latin1_swedish_ci,and my server default collation is: utf8mb4_unicode_ci. Should I just remove those special characters or what? this the php code:

<?php 
require "conn.php";
$sql = "select * from produit";
$result = mysqli_query($conn, $sql); // result contient tous les produits 
$response = array(); // on déclare un array
// pour chaque ligne de la table 
while ($row = mysqli_fetch_array($result)) { 
array_push($response, array("id_produit"=>$row[0] , "nom"=>$row[1], "catégorie"=>$row[2], "description"=>$row[3], "type"=>$row[4], "prix"=>$row[5], "qte_stock"=>$row[6]));
}
echo json_encode(array("server_response"=> $response));
mysqli_close($conn);

 ?>

show create table produit :

CREATE TABLE `produit` (
 `id_produit` int(11) NOT NULL AUTO_INCREMENT,
 `nom` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `catégorie` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `description` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `type` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `prix` int(11) DEFAULT NULL,
 `qte_stock` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_produit`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

I use utf8_bin with french characters. Also, see if you can set UTF characters with the function `mysqli_set_charset($conn,"utf8");`. — Jachinair, Mar 27 '17 at 12:21
i think you need to use html unicode utf-8 value (https://www.w3schools.com/charsets/ref_utf_symbols.asp) and change db/table Collation is utf8_unicode_ci — Jayanta, Mar 27 '17 at 12:26
You should `ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4` before you encounter characters that cannot be converted to latin1. — Rick James, Mar 30 '17 at 03:55
Best way for JSON field : encoding *utf8mb4* and collation *utf8mb4_bin* (that now default in MySQL 8) — Boss COTIGA, Nov 08 '21 at 12:39

score 1 · Accepted Answer · answered Mar 28 '17 at 21:18

1

For all those wondering how to solve the problem with french characters and JSON trust me I've tried everything these days the only thing that works is :

echo json_encode(array("server_response"=>$response), JSON_UNESCAPED_UNICODE);

here's the link for more information http://php.net/manual/fr/function.json-encode.php thank god for that, I lost hope for a second

answered Mar 28 '17 at 21:18

Valentina_Siii

191
3
14

Yes, that generates UTF-8 byte sequences. In MySQL such is called `CHARACTER SET utf8mb4`. You mentioned `utf8`. It might work, but it is missing Emoji and some Chinese characters. – Rick James Mar 30 '17 at 03:54
@r Thank you for the advice but – Valentina_Siii Mar 30 '17 at 21:17
@RickJames Thank you for the advice but it's my hopeless solution, I don't have any other choice, by the way I read one of your answers and I was amazed by your experience I was realy hoping that you could answer me, do you have any other solutions instead of mine please? – Valentina_Siii Mar 30 '17 at 21:24
Please provide the code that includes `mysqli_set_charset`, `SHOW CREATE TABLE`, and anything else relevant. There are several places where one must specify utf8 in some fashion; missing any one leads to disaster. There is not quite enough info for me to point you to a specific part of http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . – Rick James Mar 30 '17 at 21:48
@RickJames That's the answer I saw before which helped me change many things in my database, I edited my question it contains now the create table code, and for mysqli_set_charset I tried to use it but it always gives errors so I removed it – Valentina_Siii Mar 30 '17 at 22:04
Well, you really need to do `SET NAMES` in some way. You seem to be using `mysqli_*`, so I am surprised that charset failed. Do you have more details? – Rick James Mar 30 '17 at 22:12
I'm wondering if I have to set something before importing the database to the web host, I think I'm missing something @RickJames – Valentina_Siii Mar 30 '17 at 22:26
How are you doing the "import"? – Rick James Mar 31 '17 at 03:29
@RickJames I use the character set of the file as "utf-8" and Ieave all the other options at their default values – Valentina_Siii Mar 31 '17 at 14:52
"utf-8" is valid _outside_ mysql. Inside, you need "utf8" or "utf8mb4". Are you using `LOAD DATA`? Or some other mechanism to "import"? – Rick James Mar 31 '17 at 17:41
@RickJames I'm just a beginner, this is the first time I use Mysql, I used to work with oracle, anyway so if you mean by LOAD DATA browsing the computer and selecting a file then Yes. also I descovered that the default import comments all this part: '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;' – Valentina_Siii Mar 31 '17 at 18:33
[_This_](https://dev.mysql.com/doc/refman/5.6/en/load-data.html) is what I meant. – Rick James Mar 31 '17 at 21:16
No that's not what I used, anyway I think that reading one or two answers won't be enough I have many many things to learn, so if you can suggest me some good references I'll be greatful, and thank you so much for everything @RickJames – Valentina_Siii Apr 01 '17 at 13:55

What is the best collation when using JSON and some special characters

1 Answers1