Archived: Sending Text in Latin1 Encoding to MySQL 5

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 5 database. But I skipped question of how to send non-ASCII characters properly to MySQL 5 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 5 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 5 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 5 to use the default settings of character_set_client=latin1 and character_set_connection=latin1 to see how MySQL 5 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 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:

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
   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

 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