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

Question

I am migrating my site into php mysqli from php mysql_* methods.

I had following code that did the job:

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Without this query my string characters (in Georgian language) were written with question marks. For example it was written ????????? instead of გამარჯობა

So since it did its job I was happy, but now I cannot do the same with mysqli.

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Can anyone please help me out? Thanks.

J-16 SDiZ · Accepted Answer · 2013-09-19 20:26:25Z

17

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

edited Sep 19, 2013 at 20:26

J-16 SDiZ

27k4 gold badges67 silver badges87 bronze badges

answered Nov 17, 2012 at 19:00

Samuel Cook

16.9k7 gold badges56 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Davit Over a year ago

if (!$mysqli->set_charset("utf8")) { printf("Error loading character set utf8: %s\n", $mysqli->error); } else { printf("Current character set: %s\n", $mysqli->character_set_name()); } but does not work.

Davit Over a year ago

OK I made it into work. GREAT THANK YOU. I will accept your answer ASAP.

Anirudh Ramanathan · Accepted Answer · 2012-11-17 19:00:31Z

1

You can use mysqli_set_charset

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.

However, to set collation, you will still have to use the SET NAMES query.

answered Nov 17, 2012 at 19:00

Anirudh Ramanathan

46.8k24 gold badges137 silver badges195 bronze badges

Comments

ro0ter · Accepted Answer · 2012-11-17 19:01:09Z

1

http://php.net/manual/en/mysqli.set-charset.php

or

mysqli->set_charset("utf8")

answered Nov 17, 2012 at 19:01

ro0ter

4392 silver badges11 bronze badges

Comments

Fred Gandt · Accepted Answer · 2017-07-04 01:44:51Z

A PHP feature request/bug report was filed...

See https://bugs.php.net/bug.php?id=52267 which garnered the response from [email protected]:

...use mysqli_set_charset() to set the charset and then SET NAMES to change the collation.

He/she also links to http://dev.mysql.com/doc/refman/5.1/en/mysql-set-character-set.html

This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

And I'll link to http://dev.mysql.com/doc/refman/5.6/en/charset-collate.html which shows how to make queries using whatever collation suits that query.

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:

With ORDER BY:
SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci;
With AS:
SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1;
With GROUP BY:
SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci;
With aggregate functions:
SELECT MAX(k COLLATE latin1_german2_ci) FROM t1;
With DISTINCT:
SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1;
With WHERE:
SELECT * FROM t1 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; SELECT * FROM t1 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;
With HAVING:
SELECT k FROM t1 GROUP BY k HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

A PHP feature request/bug report was filed...

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

A PHP feature request/bug report was filed...

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related