php mysql SET NAMES 'utf8' COLLATE 'utf8_unicode_ci' doesn't work with mysqli

Question

I am migrating my site into php mysqli from php mysql_* methods.

I had following code that did the job:

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Without this query my string characters (in Georgian language) were written with question marks. For example it was written ????????? instead of გამარჯობა

So since it did its job I was happy, but now I cannot do the same with mysqli.

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Can anyone please help me out? Thanks.

Answer 1

It is not recommended to use mysqli query in order to set names but rather mysqli::set_charset

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->set_charset("utf8");

Answer 2

You can use mysqli_set_charset

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.

However, to set collation, you will still have to use the SET NAMES query.

Answer 3

http://php.net/manual/en/mysqli.set-charset.php

or

mysqli->set_charset("utf8")

Answer 4

A PHP feature request/bug report was filed...

See https://bugs.php.net/bug.php?id=52267 which garnered the response from [email protected]:

...use mysqli_set_charset() to set the charset and then SET NAMES to change the collation.

He/she also links to http://dev.mysql.com/doc/refman/5.1/en/mysql-set-character-set.html

This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

And I'll link to http://dev.mysql.com/doc/refman/5.6/en/charset-collate.html which shows how to make queries using whatever collation suits that query.

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:

• With ORDER BY:
  SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci;

• With AS:
  SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1;

• With GROUP BY:
  SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci;

• With aggregate functions:
  SELECT MAX(k COLLATE latin1_german2_ci) FROM t1;

• With DISTINCT:
  SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1;

• With WHERE:
  SELECT * FROM t1 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; SELECT * FROM t1 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

• With HAVING:
  SELECT k FROM t1 GROUP BY k HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;