PHP 連結MySQL資料庫 解決亂碼問題

基本上 MySQL 資料庫語系設定分五個等級：
1. 伺服器級，即 mysql.ini 的設定。
2. 資料庫級。
3. 資料表級。
4. 資料欄位級。
5. 連線校對級。

--------------------------------------------------------
一、設定：MySQL configuration file
LINUX 下可透過修改 /etc/my.cnf 檔案
Windows 下可透過修改 my.ini 檔案

[mysqld]

default-character-set=utf8
default-collation=utf8_general_ci
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_unicode_ci'
skip-character-set-client-handshake
設定檔內加入「skip-character-set-client-handshake」是為了抑制MySQL內部自行進行變換。

[client]
default-character-set=utf8

Restart the MySQL
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

http://kosalads.blogspot.tw/2013/03/mysql-55-how-to-change-mysql-default.html
http://www.chou-it.com/info/infra/db/mysql_01.html
http://cameronyule.com/2008/07/configuring-mysql-to-use-utf-8/
--------------------------------------------------------

二、創建資料庫時候直接指定編碼和排序規則

CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `content` (

  `id` int(11) NOT NULL auto_increment,

  `language` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL default '',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

--------------------------------------------------------

三、PHP程式

選取資料庫的後面及query()之前加上mysql_query("set names 'utf8'");

這樣一來就不用在每段查詢語法都要個別指定編碼，省事許多，

mysql_connect("localhost:3306","root","1234");
mysql_select_db("mytest");
mysql_query("set names 'utf8'");

$rs=mysql_query("select * from table_A");
print_r(mysql_fetch_row($rs));
mysql_close();

即完成資料庫連線,顯示資料以及中文編碼問題。

http://www.dotblogs.com.tw/jellycheng/archive/2010/11/18/19535.aspx

--------------------------------------------------------
註解：

SET NAMES UTF8 等於設定

SET character_set_client = utf8;

SET character_set_results = utf8; 

SET character_set_connection = utf8

SET character_set_system utf8

SET character_set_server = utf8;

SET character_set_database = utf8;

SET collation_server = utf8_general_ci;

SET collation_database = utf8_general_ci;

--------------------------------------------------------

範例：

class DB_CONNECT {
    function __construct() {
        $this->connect();
    }
    function __destruct() {
        $this->close();
    }

    // Function to connect with database
    function connect() {
  //require_once('db_config.php'); //Befor PHP 5.2
        require_once __DIR__ . '/db_config.php'; //AFTER PHP 5.3 
        // Connecting to mysql database
        $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());
        // Selecing database
        $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());
  // 設定 UTF8
  mysql_query('SET NAMES UTF8;');

        // returing connection cursor
        return $con;
    }

     function close() {
        mysql_close();
    }
 }

$db = new DB_CONNECT();
 $result = mysql_query("SELECT * FROM products") or die(mysql_error());

if (mysql_num_rows($result) > 0) {
    $response["products"] = array();

    while ($row = mysql_fetch_array($result)) {
        // temp user array
        $product = array();
        $product["pid"] = $row["pid"];
        $product["name"] = $row["name"];
        $product["price"] = $row["price"];
  $product["description"] = $row["description"];
        $product["created_at"] = $row["created_at"];
        $product["updated_at"] = $row["updated_at"];

        // product 放入 products array
        array_push($response["products"], $product); 
    }
    // success
    $response["success"] = 1;
 
 $encode = json_encode($response);
)