We can explicitly set the char set to utf8 when initializing PDO, just add "
SET NAMES utf8 COLLATE utf8_unicode_ci;
Here is a two in one answer.
You can set this in the DSN or as MYSQL_ATTR_INIT_COMMAND (connection options).
DSN is better, i think.
$connect = new PDO( "mysql:host=$host;dbname=$db;charset=utf8", $user, $pass, array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8" ) );
If you specify
UTF-8 you are working with the default collation of
utf8_general_ci, unless your db table or field uses something different.
If you want the whole server to respond with this default collation then use configuration directives:
So you don't have to specify it on connection everytime.
The collations affect the sorting of chars and is set on the table and fields in your database. These settings are respected, when querying the table. Make sure they are set. Use UTF-8 names with the collation set in your db.
"People should know char set and collation are 2 different things."
Let's Quote from the MySQL Manual to proof this:
SET NAMES 'charset_name'statement is equivalent to these three statements:
SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;
character_set_connectionto charset_name also implicitly sets
collation_connectionto the default collation for charset_name.
My answer: It works implicitly, unless your tables changes this explicitly.
Question from comment:
How to make sure I don't mess things up as my tables are not the default collation utf8_general_ci?
Example: Column collation overrides table collation
CREATE TABLE t1 ( col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci ) CHARACTER SET latin1 COLLATE latin1_bin;
If both CHARACTER SET X and COLLATE Y are specified on a column, character set X and collation Y are used. The column has character set
utf8 and collation
utf8_unicode_ci as specified in the table column, while the table is in latin1 + latin1_bin.
Example: in general table collation is used
If collation is not explicitly specified on a column/Field, then the table collation is used:
CREATE TABLE t1 ( col1 CHAR(10) ) CHARACTER SET latin1 COLLATE latin1_bin;
col1 has collation latin1_bin.
If you want
utf8_unicode_ci collation, set it to your tables in general or to the columns/fields.