Transmitting Non-ASCII Characters between PHP and MySQL

This section provides information on how non-ASCII characters are transmitted between PHP and MySQL.

To understand why the test in the previous tutorial failed for some encoding types, we need to understand how data were transmitted between PHP and MySQL.

Based on the manual, MySQL communicates with client programs, like PHP scripts or SQL Monitor, in character mode and applies encoding conversions. Here is a simple diagram that shows how MySQL manages the character conversion while communicating with client programs:

Client program sends SQL statement
   |
   | Encoding: A, defined as "character_set_client"
   v
MySQL server - Conversion from encoding A to encoding B
   |
   | Encoding: B, defined as "character_set_connection"
   v
MySQL server - Execution to store data
MySQL server - Conversion from encoding B to encoding C
   |
   | Encoding: C, defined by text column encoding 
   v
MySQL server - Storage
...
MySQL server - Storage
   |
   | Encoding: C, defined by text column encoding
   v
MySQL server - Execution to fetch data
MySQL server - Conversion from encoding C to encoding D
   |  
   | Encoding: D, defined as "character_set_results"
   v
Client program receives result set

As you can see from this diagram, there are 4 character set encodings, 3 conversion processes, involved from the point where a client program sends a SQL statement to the point where the client program receives the result data:

Obviously, if you want to ensure your text data to be correctly transmitted, stored, and fetched into and from MySQL server, you must properly set encodings A, B, C, and D. See the next section for detail discussions.

Table of Contents

 About This Book

 PHP Installation on Windows Systems

 Integrating PHP with Apache Web Server

 charset="*" - Encodings on Chinese Web Pages

 Chinese Characters in PHP String Literals

 Multibyte String Functions in UTF-8 Encoding

 Input Text Data from Web Forms

 Input Chinese Text Data from Web Forms

 MySQL - Installation on Windows

 MySQL - Connecting PHP to Database

MySQL - Character Set and Encoding

 Specifying Character Set for Text Columns

 Creating a Table with Multiple Character Sets

 Checking Character Set Setting

 Storing ASCII Characters in Non-ASCII Columns

 Storing Non-ASCII Characters with Encoded Bytes

Transmitting Non-ASCII Characters between PHP and MySQL

 Viewing Character Set Variables

 Non-ASCII Test Result Analysis

 Fetching Non-ASCII Text from MySQL

 MySQL Encoding Issue Example

 MySQL - Sending Non-ASCII Text to MySQL

 Retrieving Chinese Text from Database to Web Pages

 Input Chinese Text Data to MySQL Database

 Chinese Text Encoding Conversion and Corruptions

 Archived Tutorials

 References

 Full Version in PDF/EPUB