Php pdo mysql charset utf8

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.

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:

Пример #1 Problems with setting the character set with SQL

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

Замечание: 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.

Пример #2 Setting the character set example: mysqli

$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 () );

Пример #3 Setting the character set example: pdo_mysql

Note: This only works as of PHP 5.3.6.

Пример #4 Setting the character set example: mysql

$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 );
?>

Источник

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

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

// Will NOT affect $mysqli->real_escape_string();
$mysqli -> query ( «SET NAMES utf8mb4» );

// Will NOT affect $mysqli->real_escape_string();
$mysqli -> query ( «SET CHARACTER SET utf8mb4» );

// But, this will affect $mysqli->real_escape_string();
$mysqli -> set_charset ( ‘utf8mb4’ );

// But, this will NOT affect it (UTF-8 vs utf8mb4) — 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 (up to 3 byte UTF-8 Unicode Encoding). The string «UTF-8» is not valid, as using «UTF-8» will fail to change the character set and will throw an error.

Example #2 Setting the character set example: mysqli

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

echo ‘Initial character set: ‘ . $mysqli -> character_set_name () . «\n» ;

if (! $mysqli -> set_charset ( ‘utf8mb4’ )) printf ( «Error loading character set utf8mb4: %s\n» , $mysqli -> error );
exit;
>

echo ‘Your current character set is: ‘ . $mysqli -> character_set_name () . «\n» ;
?>

Example #3 Setting the character set example: pdo_mysql

Источник

PDO – Set character encoding of a database

The type of character set used on the HTML page must match the character set used in the database table. In this tutorial, we’ll specify the utf-8 encoding for PHP, MySQL database, HTML page, and Notepad++ editor.

There are several ways to set the character encoding of a database. The recommended method is appending ;charset=charsetName to the DSN string. But older PHP versions ignore this option. Here we’ll explore all available options to set the character encoding for a database:

Specifying character set in the DSN string

To get UTF-8 charset you can specify that in the DSN:

Specifying character set by executing SET NAMES utf8

Prior to PHP 5.3.6, the charset option was ignored. For older versions use the following method:

If you also need to set collation:

exec('set names utf8 COLLATE collateName'); //For utf8 the default collation is utf8_general_ci $dbh->exec('set names utf8 COLLATE utf8mb4_unicode_ci');

Specifying character set in PDO constructor’s fourth parameter

You also can set the character encoding by creating an options array for PDO constructor’s fourth parameter:

 'SET NAMES utf8']; $db = new PDO($dsn, $user, $password, $options);

Note: The above example only works with the MySQL database.

Output Multibyte (i.e. UTF-8) Data

1. Store data with the UTF-8 character encoding.
2. Tell PHP to use UTF-8 character encoding. It’s easiest to do this in your php.ini file like this:
   default_charset = «UTF-8»;
3. Display data with the UTF-8 character encoding:

• For PHP scripts and dynamically generated HTML files send content-type header:

1. Configure your code editor or IDE to use UTF-8 character encoding, the following figure shows the utf-8 configuration setting for the Notepad++ code editor:

Working with Databases:

Источник

PDO::__construct

Создаёт экземпляр PDO, предоставляющий подключение к необходимой базе данных.

Список параметров

Имя источника данных или DSN, содержащее информацию, необходимую для подключения к базе данных.

В общем, DSN состоит из имени драйвера PDO, за которым следует двоеточие и специфический синтаксис подключения драйвера PDO. Дополнительную информацию можно получить из раздела Документация по специфическим драйверам PDO.

Параметр dsn поддерживает три разные метода указания аргументов, необходимых для создания соединения с базой данных:

Параметр dsn состоит из строки uri: с последующим URI, который определяет расположение файла, содержащего строку DSN. URI может указывать на локальный файл или удалённый URL.

uri:file:///path/to/dsnfile

dsn состоит из имени name , которое соответствует параметру pdo.dsn. name в php.ini , определяющему строку DSN.

Замечание:

Псевдоним должен быть определён в php.ini , но не в .htaccess или httpd.conf

Имя пользователя для строки DSN. Этот параметр опционален для некоторых драйверов PDO.

Пароль для строки DSN. Этот параметр опционален для некоторых драйверов PDO.

Массив ключ=>значение специфичных для драйвера настроек подключения.

Ошибки

PDO::__construct() выбрасывает исключение PDOException , если попытка подключения к запрашиваемой базе данных завершается с ошибкой, независимо от того, какой PDO::ATTR_ERRMODE установлен в данный момент.

Примеры

Пример #1 Создание экземпляра PDO через вызов драйвера

/* Подключение к базе данных MySQL с помощью вызова драйвера */
$dsn = ‘mysql:dbname=testdb;host=127.0.0.1’ ;
$user = ‘dbuser’ ;
$password = ‘dbpass’ ;

$dbh = new PDO ( $dsn , $user , $password );

Пример #2 Создание экземпляра PDO через вызов URI

В следующем примере предполагается, что файл /usr/local/dbconnect существует с правами доступа, которые позволяют PHP прочитать файл. Файл содержит PDO DSN для подключения к базе данных DB2 через драйвер PDO_ODBC:

odbc:DSN=SAMPLE;UID=john;PWD=mypass

PHP-скрипт может создать подключение к базе данных путём простой передачи параметра uri: , указывающим на файл URI:

/* Подключение к базе данных ODBC с помощью вызова драйвера */
$dsn = ‘uri:file:///usr/local/dbconnect’ ;
$user = » ;
$password = » ;

$dbh = new PDO ( $dsn , $user , $password );

Пример #3 Создание экземпляра PDO, используя псевдоним

В следующем примере предполагается, что php.ini содержит следующую запись, позволяющую подключиться к базе данных MySQL, используя только псевдоним mydb :

[PDO] pdo.dsn.mydb="mysql:dbname=testdb;host=localhost"

/* Подключение к базе данных ODBC, используя псевдоним */
$dsn = ‘mydb’ ;
$user = » ;
$password = » ;

$dbh = new PDO ( $dsn , $user , $password );

Источник

PDO – Set character encoding of a database

The type of character set used on the HTML page must match the character set used in the database table. In this tutorial, we’ll specify the utf-8 encoding for PHP, MySQL database, HTML page, and Notepad++ editor.

There are several ways to set the character encoding of a database. The recommended method is appending ;charset=charsetName to the DSN string. But older PHP versions ignore this option. Here we’ll explore all available options to set the character encoding for a database:

Specifying character set in the DSN string

To get UTF-8 charset you can specify that in the DSN:

Specifying character set by executing SET NAMES utf8

Prior to PHP 5.3.6, the charset option was ignored. For older versions use the following method:

If you also need to set collation:

exec('set names utf8 COLLATE collateName'); //For utf8 the default collation is utf8_general_ci $dbh->exec('set names utf8 COLLATE utf8mb4_unicode_ci');

Specifying character set in PDO constructor’s fourth parameter

You also can set the character encoding by creating an options array for PDO constructor’s fourth parameter:

 'SET NAMES utf8']; $db = new PDO($dsn, $user, $password, $options);

Note: The above example only works with the MySQL database.

Output Multibyte (i.e. UTF-8) Data

1. Store data with the UTF-8 character encoding.
2. Tell PHP to use UTF-8 character encoding. It’s easiest to do this in your php.ini file like this:
   default_charset = «UTF-8»;
3. Display data with the UTF-8 character encoding:

• For PHP scripts and dynamically generated HTML files send content-type header:

1. Configure your code editor or IDE to use UTF-8 character encoding, the following figure shows the utf-8 configuration setting for the Notepad++ code editor:

Working with Databases:

Источник