Asked  7 Months ago    Answers:  5   Viewed   35 times

So I have set mysql for full utf8 support:

my.cnf:

[mysqld]
character_set_client=utf8
character-set-server=utf8
collation_server=utf8_general_ci

and

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like "coll%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Now, I correctly store and get (and json_encode) utf8 characters in my web app with my mysql queries but I have a problem with phpmyadmin. The problem is that phpmyadmin displays those international characters as garbled text.

έλεος

sadaa

just to clarify things out, I'm talking about NEW DATA, not already stored data which may be corrupted.

the session variables for phpmyadmin are:

show variables like "char%";
Variable_name   Value   
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    utf8
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

show variables like "coll%" 
Variable_name   Value   
collation_connection    utf8mb4_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci

The only way I have found till now for the international utf8 characters to display properly in phpmyadmin and not as garbled text is to run

SET NAMES utf8

every time before a client app (php) query which: a) is weird because as you can see everything is properly utf8 b) i would like to avoid running that query every time

is this a phpmyadmin only problem or a general data encoding problem with my settings? Either way, how can I solve this? Preferrably just with configuration files settings, not having to run the SET NAMES utf8 query each time.

thanks in advance.

 Answers

48

Answering my own question.

It seems it was not a phpmyadmin problem but a general encoding problem.

The problem was I was not setting my client connection charset.

here's what I got if I displayed the variables from a php file:

{"Variable_name":"character_set_client","Value":"latin1"}
{"Variable_name":"character_set_connection","Value":"latin1"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"latin1"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"/usr/share/mysql/charsets/"}



{"Variable_name":"collation_connection","Value":"latin1_swedish_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}

Initially i tried adding in my.cnf

[client]
default-character-set=utf8

IT DOES NOT WORK.

so the solution was to add:

charset=UTF8

in the PDO initialization

so after that I got the proper variable values:

{"Variable_name":"character_set_client","Value":"utf8"}
{"Variable_name":"character_set_connection","Value":"utf8"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"utf8"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"/usr/share/mysql/charsets/"}



{"Variable_name":"collation_connection","Value":"utf8_general_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}
Friday, May 28, 2021
 
MDDY
answered 7 Months ago
89

This may be a job for the mb_detect_encoding() function.

In my limited experience with it, it's not 100% reliable when used as a generic "encoding sniffer" - It checks for the presence of certain characters and byte values to make an educated guess - but in this narrow case (it'll need to distinguish just between UTF-8 and ISO-8859-1 ) it should work.

<?php
$text = $entity['Entity']['title'];

echo 'Original : ', $text."<br />";
$enc = mb_detect_encoding($text, "UTF-8,ISO-8859-1");

echo 'Detected encoding '.$enc."<br />";

echo 'Fixed result: '.iconv($enc, "UTF-8", $text)."<br />";

?>

you may get incorrect results for strings that do not contain special characters, but that is not a problem.

Wednesday, March 31, 2021
 
keisar
answered 9 Months ago
11

– is common mojibake for an en dash (), which is a different character from a hyphen.

It is the result of taking the UTF-8–encoded form of the dash (0xe2 0x80 0x93) and incorrectly assuming that it is actually encoded using Windows-1252.

Interpreting those three bytes as Windows-1252: 0xe2, 0x80 and 0x93 separately represent â, and .

Assuming the offending character is in the blurb field, if you query SELECT HEX(blurb) FROM tpf_parks (with a suitable WHERE clause), you will see the hex encoding of the offending bytes.

If you see E28093 in there, then the database value is correctly encoded as UTF-8 and there will be a character encoding mismatch in your client or server configuration.

If, however, you see C3A2E282ACE2809C, then the character has already been encoded incorrectly in the database — i.e. interpreted incorrectly, then saved as the UTF-8 representation of those 3 characters. If this is the case you'll need to update the data to fix the issue. You could do this using iconv:

$fixedData = iconv("utf-8", "windows-1252", $badData);

This will convert the doubly-converted bytes back to the UTF-8 encoding.

Saturday, May 29, 2021
 
Hilmi
answered 7 Months ago
33

Have you tried adding

SET NAMES 'utf8';

to your sql dump?

The thing with utf8 or encodings in general is that in order to be successfull, you have to make sure that:

  • the file is encoded utf8 without signature
  • the default encoding of the mysql server is set to utf8
  • the connection is utf8 (that's why you put SET NAMES 'utf8' into your sql-file).
  • all tables and columns have the right encoding and charset
  • all your webfiles have to be utf8 encoded as well. And it doesn't work to just add the correct header. You have to open the file, check if the encoding is utf8, if not, cut everything, change the encoding to utf8 and paste everything back. It doesn't work, if you just change the encoding and save the file!
Friday, July 2, 2021
 
Angolao
answered 5 Months ago
65

Finally my problem is solved!. I dont exactly know which commands worked for me but these are the last commands i run to solve the problem.

sudo apt-get install -f

sudo apt-get remove --purge apache* && sudo apt-get remove --purge libapache* && sudo apt-get remove --purge php*

sudo apt-get remove --purge php5-common php5-cli

sudo apt-get install apache2 php5 libapache2-mod-php5 mysql-server php5-mysql

sudo service apache2 restart sudo apt-get install phpmyadmin sudo service apache2 restart

sudo apt-get remove --purge php5-common php5-cli

sudo apt-get install apache2 php5 libapache2-mod-php5 mysql-server php5-mysql

sudo service apache2 restart

and browse it on http://localhost/phpmyadmin/

If your problem still not solved then first install node js by-

sudo apt-get update

sudo apt-get install nodejs

then again try all commands because i have also installed node js before running above commands i think node js is related to this javascript problem or refer questions-https://askubuntu.com/questions/308523/i-have-broken-ubuntu and https://askubuntu.com/questions/307761/php-server-setup-not-working

Wednesday, August 25, 2021
 
Isky
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share