utf8 in database - set default_charset in php.ini

Question

I have set utf8 in mysql database

now it shows

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

I have the below in the php code

mysqli_query("SET NAMES 'utf8'");

and

$con = mysqli_connect('host','user','pass','database');
mysqli_query($con,"SET NAMES 'utf8'");

Since I set utf8 in database, I am planning to remove

mysqli_query("SET NAMES 'utf8'");

and

mysqli_query($database,"SET NAMES 'utf8'");

from PHP code

I was told that I can remove the above code but I need to add

mysqli_set_charset($con,"utf8");

in the PHP code.

Instead of adding mysqli_set_charset($con,"utf8") in the code, can I set

default_charset = "UTF-8"

in php.ini?

my.cnf is

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'

You're mixing `mysql_` and `mysqli_` functions in your question. They shouldn't be mixed like that. If it's a typo, fix your question. — Machavity, Oct 12 '15 at 17:48
please note that standard `UTF8` in MySQL is **NOT** the full UTF8 Character set. If you want/ need the full UTF8 character set saved in your database you need to use `UTF8mb4` in MySQL. — Martin, Oct 12 '15 at 18:05
see https://stackoverflow.com/questions/279170/utf-8-all-the-way-through — Martin, Oct 12 '15 at 18:07

score 2 · Answer 1 · answered Oct 12 '15 at 17:48

2

Instead of adding mysqli_set_charset($con,"utf8") in the code, can I set default_charset = "UTF-8" in php.ini?

No, that does not affect the database connection character set.

And even the MySQL extension settings offer nothing in that regard – and neither do those for MySQLi.

It is a per-connection setting after all.

But if you put your code to establish the database connection into one file that you include wherever it is needed, that should be enough.

answered Oct 12 '15 at 17:48

CBroe

91,630
14
92
150

so, mysqli_set_charset($con,"utf8"); is a must have after establishing database connection? – Jeff Oct 12 '15 at 17:55
Unless you can configure the default value in the MySQL server settings directly: Yes. (https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_character_set_client) – CBroe Oct 12 '15 at 18:00
in my.cnf, I have [client] default-character-set=utf8 and [mysqld] default-character-set=utf8 – Jeff Oct 12 '15 at 18:07
2

Strongly recommend always putting it into your code. That way it your code is not dependent on the MySQL server configuration which could change over time. – Steve E. Oct 12 '15 at 18:27
Thanks @SteveE., just to know, if I have [client] default-character-set=utf8 and [mysqld] default-character-set=utf8 in my.cnf, can I avoid having mysqli_set_charset($con,"utf8"); in the code? – Jeff Oct 12 '15 at 18:32
1

The [client] section of my.cnf applies to the mysql command line tool. It does not affect the server. It really is better to be specific in your code about the connection character-set rather than leave it to configuration options. The impact on performance is negligible. – Steve E. Oct 12 '15 at 18:48

score 0 · Answer 2 · answered Oct 12 '15 at 18:04

The question is in reference to MySQL so setting up a config in php.ini won't help much in this case. What i suggest is to update the my.cnf file in mysql

more about my.cnf settings can be found here

[client] 
default-character-set=utf8

[mysqld] 
character-set-server=utf8 
default-character-set=utf8 
default-collation=utf8_unicode_ci 
character-set-client = utf8

i took the above part from this thread, which discusses the same issue but for both PHP and MySQL. Hope this helps!

I have updated my question with my.cnf if I have those settings in my.cnf, can I avoid having mysqli_set_charset($con,"utf8"); in the code? — Jeff, Oct 12 '15 at 18:19

utf8 in database - set default_charset in php.ini

2 Answers2