Insert Update to MYSQL with PHP 5.5 UTF-8 issues

Question

I can't understand whats problem. I write data to mysql with php and utf-8 issues

Write in HTML form Binəqədi r. Çiçək qəs

Writed database Bin?q?di r. ÃÃÂ

When I use mysqli_set_charset($conn,"utf8"); in insert and update query

Writed database Bin?q?di r. Çiç?k q?s

Write data manually direct to database and use mysqli_set_charset($conn,"utf8"); in select works normal.

How can I fix it? INSERT and UPDATE with UTF-8. This is my insert code

public function insert($sql) {
    $conn = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    mysqli_set_charset($conn,"utf8");
//  $conn->set_charset("utf8"); use same this.
    if ($conn->query($sql) === TRUE) {
        return 1;
    } else {
        return 0;
    }
    $conn->close();
}

NOTE: This issues on Linux and OSX. In windows work goods. How can I fix it for linux hosting?

Answer 1

try

AddDefaultCharset utf-8

in you apache configuration

or in you php file use

header( 'content-type: text/html; charset=utf-8' );

and also try to

default_charset = "utf-8";

in you php.ini

also you can try with

  [client]
  default-character-set=utf8

  [mysql]
  default-character-set=utf8

  [mysqld]
  character-set-client-handshake = false #force encoding to uft8
  character-set-server=utf8
  collation-server=utf8_general_ci

  [mysqld_safe]
  default-character-set=utf8

in your my.ini and restart mysql demon

Answer 2

Please consider all of these:

1. Before your queries, do this:

   $conn->query('SET NAMES utf8');
   

2. Be sure your file encoding is utf8 (not the html encoding). I mean the file encoding which your IDE is using.

3. Be sure about your table charset

Answer 3

This is clearly a duplicate of a well-known issue, but cannot be flagged for closure due to the bounty. So, the checklist:

1. Ensure database columns, tables, and database are set to character set utf8mb4
2. Ensure the connection is set to the same character set with $conn->set_charset("utf8mb4");
3. Ensure your server is configured to inform web browsers that it will be sending Unicode text. In PHP, do header("Content-Type: text/html;charset=utf8"); or set it up in your web server configuration.

Answer 4

Try using set names

public function insert($sql) {
    $conn = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
   // $conn->set_charset("utf8");
    $conn->query("SET NAMES 'utf8'");
    if ($conn->query($sql) === TRUE) {
        return 1;
    } else {
        return 0;
    }
    $conn->close();
}

From MySQL (http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html):

• SET NAMES 'charset_name' [COLLATE 'collation_name']

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

A SET NAMES 'charset_name' statement is equivalent to these three statements:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name. It is unnecessary to set that collation explicitly. To specify a particular collation, use the optional COLLATE clause:

• SET CHARACTER SET charset_name

SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database. A SET CHARACTER SET charset_name statement is equivalent to these three statements:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

Setting collation_connection also implicitly sets character_set_connection to the character set associated with the collation (equivalent to executing SET character_set_connection = @@character_set_database). It is unnecessary to set character_set_connection explicitly.

My opinion is that in some point the proper character set for the field description is not specified when using stored procedure. Do check again that your database/table/field have as character set utf8_general_ci

From MySQL (http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html):

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1 before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy if there are characters that are not in both character sets.

Answer 5

The gibberish you displayed seems to involve more than one error. Çiç, when Mojibaked to latin1 gives Çiç.

Please provide SELECT col, HEX(col) FROM ... for something that is 'bad'.

Meanwhile, your reply about the stored procedure -- Do SHOW CREATE PROCEDURE ... and check what character set was in effect when the procedure was created. That could be part of the problem. (I noticed that that decade-old bug report you mentioned failed to consider this aspect.)