Building Chinese Web Sites using PHP - Version 2.14, by Dr. Herong Yang
Sending Non-ASCII Text to MySQL in GBK Encoding
This section provides a test on sending non-ASCII character strings to MySQL server in GBK or GB2312 encoding.
The next test I did was to re-run the same SQL INSERT statement that included Latin1, UTF-8, GBK and Big5 characters directly. But I changed MySQL session settings with character_set_client=gbk and character_set_connection=gbk to see how MySQL applies the conversion when executing the INSERT statement.
<?php #- MySQL-Send-Non-ASCII-GBK.php #- Copyright (c) 2015, HerongYang.com, All Rights Reserved. # $con = mysql_connect("localhost", "Herong", "TopSecret"); $ok = mysql_select_db("HerongDB", $con); $test_name = "Send Non-ASCII"; # Set character_set_results mysql_query("SET character_set_results=utf8", $con); # Set character_set_client and character_set_connection mysql_query("SET character_set_client=gbk", $con); mysql_query("SET character_set_connection=gbk", $con); # Show character set encoding variables $sql = "SHOW VARIABLES LIKE 'character_set_%'"; $res = mysql_query($sql, $con); while ($row = mysql_fetch_array($res)) { print($row['Variable_name']." = ".$row['Value']."\n"); } mysql_free_result($res); # Delete the record $sql = "DELETE FROM Comment_Mixed WHERE Test_Name ='$test_name'"; mysql_query($sql, $con); print("\nNumber of rows deleted: ".mysql_affected_rows()."\n"); # Build the SQL INSERT statement $sql = <<<END_OF_MESSAGE INSERT INTO Comment_Mixed (Test_name, String_ASCII, String_Latin1, String_UTF8, String_GBK, String_Big5) VALUES ('$test_name', 'Television', 'T?l?vision', '电视机/電視機', '???', '???'); END_OF_MESSAGE; # Run the SQL statement if (mysql_query($sql, $con)) { print("\nNumber of rows inserted: ".mysql_affected_rows()."\n"); } else { print("SQL statement failed.\n"); print(mysql_errno($con).": ".mysql_error($con)."\n"); } # Get the recod back $sql = "SELECT * FROM Comment_Mixed" . " WHERE Test_Name = '$test_name'"; $res = mysql_query($sql, $con); if ($row = mysql_fetch_array($res)) { print("\nTest Name = ".$row['Test_Name']."\n"); print(" String_ASCII: 0x".bin2hex($row['String_ASCII'])."\n"); print(" String_Latin1: 0x".bin2hex($row['String_Latin1'])."\n"); print(" String_UTF8: 0x".bin2hex($row['String_UTF8'])."\n"); print(" String_GBK: 0x".bin2hex($row['String_GBK'])."\n"); print(" String_Big5: 0x".bin2hex($row['String_Big5'])."\n"); } mysql_free_result($res); mysql_close($con); ?>
Note that string literals in the source code above will not be displayed properly, because this book uses UTF-8 encoding.
Here is the output:
C:\>\local\php\php MySQL-Send-Non-ASCII-GBK.php character_set_client = gbk character_set_connection = gbk character_set_database = latin1 character_set_filesystem = binary character_set_results = utf8 character_set_server = latin1 character_set_system = utf8 character_sets_dir = \local\mysql\share\charsets\ Number of rows deleted: 1 Number of rows inserted: 1 Test Name = Send Non-ASCII String_ASCII: 0x54656c65766973696f6e String_Latin1: 0x543f3f6973696f6e String_UTF8: 0xe990a2e4bd833fe98f883fe99786e6988f3fe5a797 String_GBK: 0xe794b5e8a786e69cba String_Big5: 0x3fe8b78c3f
The output is interesting:
Last update: 2015.
Table of Contents
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
►MySQL - Sending Non-ASCII Text to MySQL
Sending Non-ASCII Text to MySQL in Latin1 Encoding
Sending Non-ASCII Text to MySQL in UTF8 Encoding
►Sending Non-ASCII Text to MySQL in GBK Encoding
Sending Non-ASCII Text to MySQL in Big5 Encoding
Sending Non-ASCII Text to MySQL in Multiple Encoding
Summary - Sending and Fetching Non-ASCII Text
Retrieving Chinese Text from Database to Web Pages