mysqlinfo-php-concepts-php-charset-4

  • Concepts

    • Character sets

  • Character sets

    • Character sets

    Character sets

    Ideally a proper character set will be set at the
    server level, and doing this is described within the » Character Set Configuration
    section of the MySQL Server manual. Alternatively, each MySQL API
    offers a method to set the character set at runtime.

    Caution

    The character set and character escaping

    The character set should be understood and defined,
    as it has an affect on every action, and includes security
    implications. For example, the escaping mechanism (e.g.,
    mysqli_real_escape_string() for mysqli,
    mysql_real_escape_string() for mysql, and
    PDO::quote() for PDO_MySQL) will adhere to
    this setting. It is important to realize that these functions will
    not use the character set that is defined with a query, so for
    example the following will not have an effect on them:

    Example #1 Problems with setting the character set with
    SQL

    <?php

    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");

    // Will NOT affect $mysqli->real_escape_string();
    $mysqli->query("SET NAMES utf8");

    // Will NOT affect $mysqli->real_escape_string();
    $mysqli->query("SET CHARACTER SET utf8");

    // But, this will affect $mysqli->real_escape_string();
    $mysqli->set_charset('utf8');

    // But, this will NOT affect it (utf-8 vs utf8) -- don't use dashes here
    $mysqli->set_charset('utf-8');

    ?>

    Below are examples that demonstrate how to properly
    alter the character set at runtime using each API.

    Note: Possible UTF-8
    confusion

    Because character set names in MySQL do not contain
    dashes, the string “utf8” is valid in MySQL to set the character
    set to UTF-8. The string “utf-8” is not valid, as using “utf-8”
    will fail to change the character set.

    Example #2 Setting the character set example:
    mysqli

    <?php
    $mysqli     = new mysqli("localhost", "my_user", "my_password", "world");

    printf("Initial character set: %s\n", $mysqli->character_set_name());

    if (!$mysqli->set_charset('utf8')) {
            printf("Error loading character set utf8: %s\n", $mysqli->error);
        exit;
    }

    echo "New character set information:\n";
    print_r( $mysqli->get_charset() );

    ?>

    Example #3 Setting the character set example: pdo_mysql

    Note: This only works as of PHP 5.3.6.

    <?php
    $pdo     = new PDO("mysql:host=localhost;dbname=world;charset=utf8", 'my_user', 'my_pass');
    ?>

    Example #4 Setting the character set example:
    mysql

    <?php
    $conn     = mysql_connect("localhost", "my_user", "my_pass");
    $db   = mysql_select_db("world");

    echo 'Initial character set: ' .  mysql_client_encoding($conn) . "\n";

    if (!mysql_set_charset('utf8', $conn)) {
        echo     "Error: Unable to set the character set.\n";
        exit;
    }

    echo 'Your current character set is: ' .  mysql_client_encoding($conn);
    ?>