MySQL and PHP: UTF-8 with Cyrillic characters [duplicate]

by

You are mixing APIs here, mysql_* and mysqli_* doesn’t mix. You should stick with mysqli_ (as it seems you are anyway), as mysql_* functions are deprecated, and removed entirely in PHP7.

Your actual issue is a charset problem somewhere. Here’s a few pointers which can help you get the right charset for your application. This covers most of the general problems one can face when developing a PHP/MySQL application.

  • ALL attributes throughout your application must be set to UTF-8
  • Save the document as UTF-8 w/o BOM (If you’re using Notepad++, it’s Format -> Convert to UTF-8 w/o BOM)

  • The header in both PHP and HTML should be set to UTF-8

    • HTML (inside <head></head> tags): 

      <meta charset="UTF-8">

    • PHP (at the top of your file, before any output): 

      header('Content-Type: text/html; charset=utf-8');

  • Upon connecting to the database, set the charset to UTF-8 for your connection-object, like this (directly after connecting)

    mysqli_set_charset($conn, "utf8"); /* Procedural approach */
$conn->set_charset("utf8");        /* Object-oriented approach */

    This is for mysqli_*, there are similar ones for mysql_* and PDO (see bottom of this answer).

  • Also make sure your database and tables are set to UTF-8, you can do that like this: 

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

    (Any data already stored won’t be converted to the proper charset, so you’ll need to do this with a clean database, or update the data after doing this if there are broken characters).

  • If you’re using json_encode(), you might need to apply the JSON_UNESCAPED_UNICODE flag, otherwise it will convert special characters to their hexadecimal equivalent.

Remember that EVERYTHING in your entire pipeline of code needs to be set to UFT-8, otherwise you might experience broken characters in your application.

In addition to this list, there may be functions that has a specific parameter for specifying a charset. The manual will tell you about this (an example is htmlspecialchars()).

There are also special functions for multibyte characters, example: strtolower() won’t lower multibyte characters, for that you’ll have to use mb_strtolower(), see this live demo.

Note 1: Notice that its someplace noted as utf-8 (with a dash), and someplace as utf8 (without it). It’s important that you know when to use which, as they usually aren’t interchangeable. For example, HTML and PHP wants utf-8, but MySQL doesn’t.

Note 2: In MySQL, “charset” and “collation” is not the same thing, see Difference between Encoding and collation?. Both should be set to utf-8 though; generally collation should be either utf8_general_ci or utf8_unicode_ci, see UTF-8: General? Bin? Unicode?.

Note 3: If you’re using emojis, MySQL needs to be specified with an utf8mb4 charset instead of the standard utf8, both in the database and the connection. HTML and PHP will just have UTF-8.

Setting UTF-8 with mysql_ and PDO

  • PDO: This is done in the DSN of your object. Note the charset attribute,

    $pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8", "user", "pass");

  • mysql_: This is done very similar to mysqli_*, but it doesn’t take the connection-object as the first argument. 

    mysql_set_charset('utf8');

More Related Contents:

  1. UTF-8 all the way through
  2. SET NAMES utf8 in MySQL?
  3. Fixing broken UTF-8 encoding
  4. Convert latin1 characters on a UTF8 table into UTF8
  5. Storing and displaying unicode string (हिन्दी) using PHP and MySQL
  6. Is “SET CHARACTER SET utf8” necessary?
  7. UTF-8 not working in HTML forms
  8. The ultimate emoji encoding scheme
  9. Is a BLOB converted using the current/default charset in MySQL?
  10. Prevent creating duplicate entries in PHP
  11. Query database then email posts from where user is subbed to
  12. Undefined index $_POST
  13. PHP Merging Row with same value (more than one column)
  14. Getting raw SQL query string from PDO prepared statements
  15. Reference: Why are my “special” Unicode characters encoded weird using json_encode?
  16. PDO::fetchAll vs. PDO::fetch in a loop
  17. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  18. Concatenating a string and primary key Id while inserting
  19. Set timezone in PHP and MySQL
  20. MySQL diacritic insensitive search (spanish accents)
  21. How do I construct a cross database query in PHP?
  22. Getting a PHP PDO connection from a mysql_connect()?
  23. Having issue with matching rows in the database
  24. php regex word boundary matching in utf-8
  25. How to convert all characters to their html entity equivalent using PHP
  26. Difference between mysql_fetch_array and mysql_fetch_row?
  27. mysql_fetch_array skipping first row
  28. 500 Internal Server Error?
  29. UTF8 Filenames in PHP and Different Unicode Encodings
  30. Aggregated query without GROUP BY

Leave a Comment