Asked  9 Months ago    Answers:  5   Viewed   80 times

I have the following problem: on a very simple php-mysqli query:

if ( $result = $mysqli->query( $sqlquery ) )
{
    $res = $result->fetch_all();
    $result->close();
}

I get strings wrongly encoded as Western encoded string, although the database, the table and the column is in utf8_general_ci collation. The php script itself is utf-8 encoded and the mysql-less parts of the script get the correct encodings. So say echo "?" works perfectly, but echo $res[0] from the previous example outputs the EF BF BD character when the file viewed in the correct UTF-8 encoding. If I manually switch the browser's encoding to Western, the mysqli sourced strings get good decoding, except for the non-western characters being replaced with "?'.

What makes it even stranger is that on my development environment this isn't happening, while on my webserver it is. The developer environment is a LAMP stack (The Uniform Server), while the webserver uses nginx.

In this case, I entered the data in the database using phpMyAdmin, and inside phpmyadmin it displays perfectly. phpMyAdmin's collation is utf-8 too. I believe that the problem must be somewhere around here, as on the same webserver, for an other site where I enter data through php (using POST) the same problem doesn't happen. On that case, the data is visible correctly both while entering and while viewing it (I mean in the php generated webpages), but the special characters are not correct in phpMyAdmin.

Can you help me start where to debug? Is it connected to php or mysql or nginx or phpMyAdmin?

 Answers

17

Use mysqli_set_charset to change the client encoding to UTF-8 just after you connect:

$mysqli->set_charset("utf8");

The client encoding is what MySql expects your input to be in (e.g. when you insert user-supplied text to a search query) and what it gives you the results in (so it has to match your output encoding in order for echo to display things correctly).

You need to have it match the encoding of your web page to account for the two scenarios above and the encoding of the PHP source file (so that the hardcoded parts of your queries are interpreted correctly).

Update: How to convert data inserted using latin-1 to utf-8

Regarding data that have already been inserted using the wrong connection encoding there is a convenient solution to fix the problem. For each column that contains this kind of data you need to do:

ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET latin1;
ALTER TABLE table_name MODIFY column_name BLOB;
ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET utf8;

The placeholders table_name, column_name and existing_column_type should be replaced with the correct values from your database each time.

What this does is

  1. Tell MySql that it needs to store data in that column in latin1. This character set contains only a small subset of utf8 so in general this conversion involves data loss, but in this specific scenario the data was already interpreted as latin1 on input so there will be no side effects. However, MySql will internally convert the byte representation of your data to match what was originally sent from PHP.
  2. Convert the column to a binary type (BLOB) that has no associated encoding information. At this point the column will contain raw bytes that are a proper utf8 character string.
  3. Convert the column to its previous character type, telling MySql that the raw bytes should be considered to be in utf8 encoding.

WARNING: You can only use this indiscriminate approach if the column in question contains only incorrectly inserted data. Any data that has been correctly inserted will be truncated at the first occurrence of any non-ASCII character!

Therefore it's a good idea to do it right now, before the PHP side fix goes into effect.

Wednesday, March 31, 2021
 
superhero
answered 9 Months ago
92

PHP starting with 7.1.0alpha2 supports UTF-8 filenames on Windows.

Thanks.

Saturday, May 29, 2021
 
CMOS
answered 7 Months ago
71

This is a FAQ for InnoDB tables. See the explanation at https://phpmyadmin.readthedocs.org/en/latest/faq.html?highlight=MaxExactCount#the-number-of-rows-for-innodb-tables-is-not-correct

Saturday, May 29, 2021
 
Student
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 6 Months ago
54

Instead of

$query = mysqli_query("SELECT * FROM subscriptions WHERE email='$email'");

use

$query = $con->query("SELECT * FROM subscriptions WHERE email='$email'");

or

$query = mysqli_query($con, "SELECT * FROM subscriptions WHERE email='$email'");

Also instead of

$insert_email = mysqli_query("INSERT INTO subscriptions (email) VALUES ('$email')");

use

$insert_email = $con->query("INSERT INTO subscriptions (email) VALUES ('$email')");

These are the only 2 errors that I could see.

Friday, September 3, 2021
 
Sheakspear Zitouni
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