21

I'm fetching data in Arabic from MySQL tables with MySQLi. So I usually use this in procedural style:

mysql_query("SET NAMES 'utf8'"); 
mysql_query('SET CHARACTER SET utf8');

Now I am using the OOP style so I am trying to see if there is something I could set rather than the above?

I only found this in PHP manual so I did it, but what about setting names to UTF8?

$mysqli->set_charset("utf8");
Mark Amery
  • 143,130
  • 81
  • 406
  • 459
sys_debug
  • 3,883
  • 17
  • 67
  • 98
  • Note that `mysqli_set_charset` exists in the procedural style too - there are no differences in the actual capabilities or behaviour of the procedural and OOP styles for using MySQLi. Also note that using `SET NAMES` or `SET CHARACTER SET` explicitly here would be wrong in either style; always prefer `mysqli_set_charset` (or its OOP equivalent). – Mark Amery May 02 '16 at 11:23
  • Do _not_ mix `mysql_*` and `mysqli_*` interfaces. Simply do _not_ use the deprecated (and removed in PHP 5.7) `mysql_*` functions. – Rick James Mar 01 '17 at 23:45

5 Answers5

27

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.

xdazz
  • 158,678
  • 38
  • 247
  • 274
  • So do I keep what I found in PHP manual + what you have here, or one would suffice? – sys_debug May 31 '12 at 08:28
  • 2
    The documentation says `...Using mysqli::query() to execute SET NAMES .. is not recommended.` http://php.net/manual/de/mysqli.set-charset.php – sofl May 31 '12 at 08:28
  • 1
    @sys_debug It looks like you don't need to do `SET NAMES 'utf8'`, `$mysqli->set_charset("utf8")` will be enough. – xdazz May 31 '12 at 08:32
  • 1
    This is confusing. You're saying to use `SET NAMES`, then immediately saying *not* to use `SET NAMES`. – Mark Amery May 02 '16 at 11:22
14

You should use

$mysqli->set_charset("utf8");

Don't use SET NAMES or SET CHARACTER SET explicitly when using MySQLi., and certainly don't use both like the question asker here originally was. Reasons that this is a bad idea:

  • Calling SET CHARACTER SET utf8 after SET NAMES utf8 actually just undoes some of the work that SET NAMES did.

  • The PHP manual explicitly warns us to use mysqli_set_charset, not SET NAMES:

    This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended. See the MySQL character set concepts section for more information.

  • Under the hood, mysqli_set_charset is just a wrapper for mysql_set_character_set from the MySQL C API (or its mysqlnd equivalent). Looking at the docs for that function, we can see the difference between it and SET NAMES:

    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()

    In other words, $mysqli->set_charset('foo') will do everything SET NAMES foo does and also ensure that mysqli_real_escape_string respects the new encoding. Admittedly, if you're only using encodings like Latin 1 and UTF 8 that strictly extend ASCII (that is, which encode all ASCII strings exactly as they would be encoded in ASCII), then using SET NAMES instead of set_charset won't break anything. However, if you're using more unusual encodings like GBK, then you could end up garbling your strings or even introducing SQL injection vulnerabilities that bypass mysqli_real_escape_string.

It's thus good practice to only use set_charset, not SET NAMES. There's nothing that SET NAMES does that set_charset doesn't, and set_charset avoids the risk of mysqli_real_escape_string behaving incorrectly (or even insecurely).

Community
  • 1
  • 1
Mark Amery
  • 143,130
  • 81
  • 406
  • 459
13

You can use this. Its realy nice for mysqli Character Set

$dbhost     = "localhost";
$dbuser     = "root";
$dbpass     = "dbpass";
$dbname     = "dbname";

$conn = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
mysqli_query($conn,"SET CHARACTER SET 'utf8'");
mysqli_query($conn,"SET SESSION collation_connection ='utf8_unicode_ci'");
Obaidul Haque
  • 916
  • 11
  • 18
12

For procedural style lovers.

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

/* change character set to utf8 */
mysqli_set_charset($conn,"utf8");
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-1
mysqli_query($conn,"SET NAMES 'latin5'");

$conn = connection string

alpc
  • 598
  • 3
  • 6
  • 2
    Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight May 02 '16 at 11:47