MySqlUnicode.php - MySQL Unicode UTF-8 Encoding Example

This section provides a tutorial example on how to send and receive non-ASCII character strings in Unicode UTF-8 encoding to and from a MySQL database server.

My is my sample script to show you how to send UTF-8 strings to a MySQL server and store them in UTF-8 encoding, MySqlUnicode.php:

<?php # MySqlUnicode.php
#- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
# 
   $con = mysql_connect('localhost');
   print "\nDefault settings...\n";
   $rs = mysql_query("SHOW VARIABLES LIKE 'character_set_%'");
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }

   print "\nUpdated settings...\n";
   $rs = mysql_query("SET NAMES 'utf8'");
   $rs = mysql_query("SHOW VARIABLES LIKE 'character_set_%'");
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }

   print "\nCreating a table in UTF-8...\n";
   $rs = mysql_query('DROP DATABASE MyBase');
   $rs = mysql_query('CREATE DATABASE MyBase CHARACTER SET utf8');
   $rs = mysql_query('USE MyBase');
   $rs = mysql_query('CREATE TABLE MyTable (ID INTEGER,'
      .' Message VARCHAR(80))');

   print "\nInserting some rows to the table...\n";
   $str = "Hello!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 1, '$str')");
   $str = "\xC2\xA1Hola!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 2, '$str')");
   $str = "\xE4\xBD\xA0\xE5\xA5\xBD!";
   $rs = mysql_query("INSERT INTO MyTable VALUES ( 3, '$str')");
      
   print "\nQuery some rows from the table...\n";
   $rs = mysql_query('SELECT * FROM MyTable WHERE ID < 10');
   print "   ".mysql_field_name($rs,0)."   "
      .mysql_field_name($rs,1)."\n";
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."=(\x".bin2hex($row[1]).")\n";
   }
   mysql_free_result($rs);   
   mysql_close($con);
?>

If you run it, you will get:

Default settings...
    character_set_client   latin1
    character_set_connection   latin1
    character_set_database   latin1
    character_set_results   latin1
    character_set_server   latin1
    character_set_system   utf8
    character_sets_dir   C:\local\MySQL\share\charsets/

Updated settings...
    character_set_client   utf8
    character_set_connection   utf8
    character_set_database   latin1
    character_set_results   utf8
    character_set_server   latin1
    character_set_system   utf8
    character_sets_dir   C:\local\MySQL\share\charsets/

Creating a table in UTF-8...

Inserting some rows to the table...

Query some rows from the table...
   ID   Message
    1   Hello!=(\x48656c6c6f21)
    2   -íHola!=(\xc2a1486f6c6121)
    3   S+ásÑ+!=(\xe4bda0e5a5bd21)

MySQL works as expected, no conversion on the SQL statements, storing strings in UTF-8, and no conversion on query result.

Last update: 2015.

Table of Contents

 About This Book

 Introduction and Installation of PHP 7.0

 PHP Script File Syntax

 PHP Data Types and Data Literals

 Variables, References, and Constants

 Expressions, Operations and Type Conversions

 Conditional Statements - "if" and "switch"

 Loop Statements - "while", "for", and "do ... while"

 Function Declaration, Arguments, and Return Values

 Arrays - Ordered Maps

 Configuring and Sending out Emails

 Retrieving Information from HTTP Requests

 Creating and Managing Sessions in PHP Scripts

 Sending and Receiving Cookies in PHP Scripts

 Controlling HTTP Response Header Lines in PHP Scripts

 MySQL Server Connection and Access Functions

 Functions to Manage Directories, Files and Images

 SOAP Extension Function and Calling Web Services

 SOAP Server Functions and Examples

 Localization Overview of Web Applications

 Using Non-ASCII Characters in HTML Documents

 Using Non-ASCII Characters as PHP Script String Literals

 Receiving Non-ASCII Characters from Input Forms

 "mbstring" Extension and Non-ASCII Encoding Management

Managing Non-ASCII Character Strings with MySQL Servers

 Storing Non-ASCII Characters in Database

 Transmitting Non-ASCII Characters with Database

MySqlUnicode.php - MySQL Unicode UTF-8 Encoding Example

 Introduction of Class and Object

 Integrating PHP with Apache Web Server

 Outdated Tutorials

 References

 PDF Printing Version