How to set a collation with mysqli?

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.

Answer

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");

Advertisement

Answer