Sending Non-ASCII Text to MySQL in Latin1 Encoding

This section provides a test on sending non-ASCII character strings to MySQL server in Latin1 encoding.

In the previous chapter, I found the answer to fetch non-ASCII characters properly from MySQL database. But I skipped question of how to send non-ASCII characters properly to MySQL database.

In the previous tutorial, non-ASCII characters were included in the SQL statement as HEX values of encoded bytes. This ensured that the entire SQL statement contains only ASCII characters and prevented any potential encoding conversions applied by MySQL server before executing the statement.

However entering encoded HEX values for non-ASCII text in SQL statements is not the right answer for large text paragraphs. I need to find a proper way to send non-ASCII text directly in the SQL statement to MySQL server.

The first test I did was to include Latin1, UTF-8, GBK and Big5 characters directly in the SQL INSERT statement. In this test, I want MySQL to use the default settings of character_set_client=latin1 and character_set_connection=latin1 to see how MySQL applies the conversion when executing the INSERT statement.

<?php 
#- MySQL-Send-Non-ASCII-Latin1.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);

# 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-Latin1.php

character_set_client = latin1
character_set_connection = latin1
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: 0x54c3a96cc3a9766973696f6e
   String_UTF8: 0xc3a7e2809dc2b5c3a8c2a7e280a0c3a6c593c2ba
      2fc3a9e280bac2bbc3a8c2a6e28093c3a6c2a9c5b8
   String_GBK: 0x3f3f3f3f3fc3ba
   String_Big5: 0x3f713f3f3fc3b7

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

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

 Input Chinese Text Data to MySQL Database

 References

 PDF Printing Version