title: How to set a collation with mysqli?
body: <p>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. <a href="https://bugs.php.net/bug.php?id=52267" rel="nofollow noreferrer">Here</a> 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.</p>
  
  <p>EDIT: now I basically use the code <a href="https://stackoverflow.com/a/20643631/1801588">YourCommonSense</a> suggested:</p>
  
  <pre><code>  $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';");
  </code></pre>
  
  <p>However, I just faced <a href="https://stackoverflow.com/q/1008287/1801588">Illegal mix of collations</a> error. While testing my connection collation through <code>mysqli_get_charset()</code> 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.</p>
  
  <p>So how to change the collation some other way?</p>
  
  <p>My former code, for archiving reasons:</p>
  
  <pre><code>  $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';");
  </code></pre>
  
  <p>EDIT2: <code>show variables like "%collation%";</code>shows the value of <code>collation_connection</code> as <code>utf8_general_ci</code>; the other collation variables are set to <code>utf8_czech_ci</code>. When running this command in Adminer, I see <code>utf8_czech_ci</code> at <code>collation_connection</code> only when I run it after any of <code>SET NAMES</code> or <code>SET COLLATION_CONNECTION</code> (see last lines of my code above for exact syntax). When I run <code>show variables</code> from php code (through <code>mysqli_query</code>), it is shown to be <code>utf8_czech_ci</code>, but comparing a value selected from a table and a value set by the client still complains about <a href="https://stackoverflow.com/q/3029321/1801588">illegal mix of collations</a>.</p>
  
  <p>Details details in my <a href="https://stackoverflow.com/q/28873538/1801588">related question</a>.</p>
  
  <p>EDIT3: I found <a href="https://stackoverflow.com/a/28893929/1801588">a good workaround for the errors</a>. 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 <a href="https://stackoverflow.com/q/28873538/1801588">follow-up question</a>. Unlike my suspicion, the originally accepted answer works, at least most of the time, so I accept it again.</p>