Set Character Set Using MySQLi

Set character set using MySQLi

It's the same:

$mysqli->query("SET NAMES 'utf8'");

From the manual:

This is the preferred way to change the charset. Using mysqli::query()
to execute SET NAMES .. is not recommended.

$mysqli->set_charset("utf8"); is just enough, let the mysqli db driver do the thing for you.

Which character set is safe for Mysqli-real_escape_string

ANY charset if you are using mysqli_set_charset()

Also, utf-8 and default latin are safe even if you aren't.

There are two sides involved in the process. A server and a client. Where client is your PHP script. So, just because escaping being done on the client side (i.e. in PHP), PHP have to know how to escape the data. This is what exactly mysqli_set_charset()- it does either let server know the incoming data encoding AND does let real_escape_string know what encoding data in.

While SET NAMES query does only part of the job, informing server only, leaving real_escape_string in the default state. However, either utf-8 and default latin are invulnerable anyway.

utf 8 - PHP and MySQLi UTF8

Please replace mysql_set_charset('utf8'); to $mysqli->set_charset("utf8") :-)

how to set UTF-8 using PHP

Before executing mysql query, use : $this->con->set_charset("utf8");

Encoding issue with MySQLi

$db->set_charset('UTF-8');

Note that MySQL is particular, the name it uses internally for the UTF-8 character set is utf8 (without dash), not the regular UTF-8.

In case of doubt, refer to SHOW CHARACTER SET; ;)

How does charset affect php when doing MySQL queries?

mysqli_set_charset($con,"utf8"); (or other code for other client libraries) declares to MySQL that the encoding in the client will be MySQL's CHARACTER SET utf8. If bytes with a different encoding are sent to (think INSERT) mysql, garbage or errors will occur.

That setting also declares that the client desires that encoding from SELECTs.

The CHARACTER SET on each column in each table may be something else (eg, "latin1"). If so, MySQL will attempt to convert the encoding during the transmission.

Caution: MySQL's CHARACTER SET utf8 is a subset of the well-known UTF-8. To get the latter, use CHARACTER SET utf8mb4 in tables and mysqli_set_charset($con,"utf8mb4"); when connecting.

Going forward, utf8mb4 is preferred in most situations.

Non-text stuff ("as-is") should be put into BLOB or VARBINARY columns -- this bypasses any checking of the encoding. (Think a .jpg or AES_ENCRYPT.)

MySQL's MD5() function returns a hex string. UNHEX(MD5('...')) return binary stuff and must be store in, say, a BINARY(16) column.

Many forms of garbled text are discussed in Trouble with UTF-8 characters; what I see is not what I stored .

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

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

a permanent way of doing mysqli-set_charset()?

You have diagnosed the basic problem correctly: While you can change the default MySQL client charset in the client machine's my.cnf or .my.cnf, these files are not used by PHP.

If you think about how PHP's MySQLi/MySQL extensions work, this will make sense -- they have nothing to do with the mysql client program and aren't going to crawl your filesystem for config files, because they use libmysql directly.

To change libmysql's actual default charset, you'll just need to rebuild libmysql. That may not be an answer you like (since you're using precompiled MySQL binaries), but it is the actual answer. The defaults are set at compile time, and then can be overridden at runtime.

If you don't want to do this and calling set_charset() annoys you, my suggestion would be to simply extend the MySQLi class and use that class in place of mysqli. i.e.:

class MyDB extends mysqli {
  // (You could set defaults for the params here if you want
  //  i.e. $host = 'myserver', $dbname = 'myappsdb' etc.)
  public function __construct($host = NULL, $username = NULL, $dbname = NULL, $port = NULL, $socket = NULL) {
    parent::__construct($host, $username, $dbname, $port, $socket);
    $this->set_charset("utf8");
  } 
} 

Typically in an application you'll have some kind of database abstraction layer anyway, so you can either have this layer use MyDB instead of mysqli, or you can have this layer be MyDB and add or override any methods you want (I've done this with simple ORM-less apps).

It's a good practice to always have some kind of database abstraction layer, even if it starts as just class MyDB extends mysqli {} because then you'll never have to search/replace your entire codebase to make small changes.

RE: your workaround, as you explain, this essentially hardcodes your entire db server to UTF-8 regardless of what clients request. Instead of having multiple databases, each with its own charset, the server only works with UTF-8 and may silently mangle data if clients connect with another charset. This is fundamentally wrong because you've effectively moved one aspect of your application's configuration (database charset) from the app/client machine to the database server where it doesn't really belong.

If you think about the application stack's layers,

[server] <=> [network] <=> [client libmysql] <=> [PHP binary] <=> [app]

then you'll understand that the "correct" place for an app-specific configuration like this is in the app itself, not elsewhere in the stack. You may not like having to specify your database's charset in PHP, but if you think about it, that's really where it belongs, because it's also where you're specifying the database itself that you want to connect to -- it's a connection parameter, not a server configuration issue. Hardcoding the charset anywhere else makes your application non-portable.

---

Related Topics