How to set a collation with mysqli?

Question

My database uses utf8_czech_ci collation and I want to set it to my database connection too. Mysqli_set_charset won't let me set collation, if I don't happen to want the default one, utf8_general_ci. Here was suggested first to set the charset through mysqli_set_charset and then to set collation by SET NAMES. So I did it and connection collation is still utf8_general_ci.

EDIT: now I basically use the code YourCommonSense suggested:

  $spojeni=mysqli_connect('mysql01','username','password', 'my_database');
  if (!$spojeni) die('Nepodařilo se připojit k databázi.');
  mysqli_set_charset($spojeni,'utf8');
  mysqli_query($spojeni, "SET COLLATION_CONNECTION = 'utf8_czech_ci';");

However, I just faced Illegal mix of collations error. While testing my connection collation through mysqli_get_charset() I found that my collation is "utf8_general_ci", not 'utf8_czech_ci' as it should be. This means that the original accepted answer ceased to work for some mysterious reason. I tested it both on localhost and hosted server, and even just after declaring the database, so some error of changing the collation incidentally seems impossible.

So how to change the collation some other way?

My former code, for archiving reasons:

  $spojeni=mysqli_connect('mysql01','username','password');
  if (!$spojeni) die('Nepodařilo se připojit k databázi.');
  mysqli_query($spojeni, "USE my_database");
  mysqli_set_charset($spojeni,'utf8');
  mysqli_query($spojeni, "SET NAMES 'utf8' COLLATE 'utf8_czech_ci';");

EDIT2: show variables like "%collation%";shows the value of collation_connection as utf8_general_ci; the other collation variables are set to utf8_czech_ci. When running this command in Adminer, I see utf8_czech_ci at collation_connection only when I run it after any of SET NAMES or SET COLLATION_CONNECTION (see last lines of my code above for exact syntax). When I run show variables from php code (through mysqli_query), it is shown to be utf8_czech_ci, but comparing a value selected from a table and a value set by the client still complains about illegal mix of collations.

Details details in my related question.

EDIT3: I found a good workaround for the errors. I still don't understand why there's the difference between variables shown in Adminer and in Mysqli query, but I already asked for it in the follow-up question. Unlike my suspicion, the originally accepted answer works, at least most of the time, so I accept it again.

Solution 1

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli=mysqli_connect('mysql01','username','password', 'my_database');
$mysqli->set_charset('utf8mb4');
// setting collation is optional and not needed 99% of time
// only if you need a specific one, like in this case
$mysqli->query("SET collation_connection = utf8mb4_czech_ci");

Solution 2

From the php documentation

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended. See the MySQL character set concepts section for more information.

An important advice from the comment of http://php.net/manual/en/mysqlinfo.concepts.charset.php by mkroese at eljakim dot nl:

Please note that MySQL's utf8 encoding has a maximum of 3 bytes and is unable to encode all unicode characters.

If you need to encode characters beyond the BMP (Basic Multilingual Plane), like emoji or other special characters, you will need to use a different encoding like utf8mb4 or any other encoding supporting the higher planes. Mysql will discard any characters encoded in 4 bytes (or more).

See https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html for more information on the matter

Comments

[Pavel V.]

It threw an error, so I found proper solution here, tested that it works, and editted your code. Anyway, your answer guided me to the solution, so +1 and accepted.

[Your Common Sense]

Yeah I forgot = sign. Now it works. You should only use proper commands, not just spam with some queries hardly understanding their meaning, like shown in the other answer.

[Andrew]

dev.mysql.com/doc/refman/5.1/en/charset-connection.html " collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter"

[Pavel V.]

This answer seems no longer valid. See my question edit.