Archived: Sending Text in UTF8 Encoding to MySQL

This section provides a test on sending non-ASCII character strings to MySQL 5 server in UTF8 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=utf8 and character_set_connection=utf8 to see how MySQL 5 applies the conversion when executing the INSERT statement.

<?php 
#- MySQL-Send-Non-ASCII-UTF8.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=utf8", $con);
  mysql_query("SET character_set_connection=utf8", $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 recode 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 with PHP 5 and MySQL 5:

C:\>\local\php\php MySQL-Send-Non-ASCII-UTF8.php

character_set_client = utf8
character_set_connection = utf8
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: 0

Number of rows inserted: 1

Test Name = Send Non-ASCII
   String_ASCII: 0x54656c65766973696f6e
   String_Latin1: 0x543f6c3f766973696f6e
   String_UTF8: 0xe794b5e8a786e69cba2fe99bbbe8a696e6a99f
   String_GBK: 0x3f3f3f3f3f
   String_Big5: 0x3f713f3f3f3f

The output is interesting:

Last update: 2015.

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

 MySQL - Sending Non-ASCII Text to MySQL

 Retrieving Chinese Text from Database to Web Pages

 Input Chinese Text Data to MySQL Database

Archived Tutorials

 Archived: Download and Install PHP 5.2.2 on Windows

 Archived: Downloading Apache 2.2.4 Binary for Windows

 Archived: Installing Apache 2.2.4 on Windows Systems

 Archived: MySQL 5.0 Download, Installation and Start

 Archived: php_mysql.dll - Configuring PHP to Use MySQL Extension

 Archived: Commonly Used php_mysql.dll Functions

 Archived: mysql_connect() - Creating MySQL Connections

 Archived: Character Set Variables on MySQL 5

 Archived: Non-ASCII Test Analysis on MySQL 5

 Archived: Fetching Non-ASCII Text from MySQL 5

 Archived: Sending Text in Latin1 Encoding to MySQL 5

Archived: Sending Text in UTF8 Encoding to MySQL

 References

 Full Version in PDF/EPUB