php / mysql / encode utf8_unicode_ci / utf8 / unicode / ci

Posted: 05/04/2013 in mysql, php
Tags: , , ,

For a few hours I struggled with the problem properly display the information in php of MySQL database encoded in “utf8_unicode_ci”. Uncle google did not say too much about this issue, so I decided to present a solution.

My database is Moodle Database, but it is not important.
Of course, I will describe both interfaces to MySQL: mysql and mysqli.

If you are getting characters similar to those below, this solution is for you

encode_utf8

encode_utf8

mysql:

<html>
<head>
<!--- optional -->
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<?php
$password = "password";
$hostname = "localhost";
$database = "database";
$username = "root";
$conn = mysql_connect($hostname, $username, $password) 
or die("Connecting to MySQL failed");
mysql_select_db($database, $conn) 
or die("Selecting MySQL database failed");
$result = mysql_query("SET NAMES utf8");
$sSQL = "SELECT * FROM whatever_you_like";
$result = mysql_query($sSQL, $conn);
while($row = mysql_fetch_object($result)) {
echo $row->name . " " . $row->summary . " ";
}
mysql_close($conn);
?>

mysqli:

<html>
<head>
   <!--- optional -->
   <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 
</head>
<body>
<?php
$con=mysqli_connect("localhost","root","password","database");
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
/* change character set to utf8 */
if (!mysqli_set_charset($con, "utf8")) {
   printf("Error loading character set utf8: %s\n", mysqli_error($con));
} else {
        printf("Current character set: %s\n", mysqli_character_set_name($con));
}
/* SQL query */ 
$result = mysqli_query($con,"SELECT * FROM whatever_you_like");
while($row = mysqli_fetch_array($result)) {
   echo $row['name'] . " " . $row['summary'];
   print '<br>';
}
/* test/check encoding */
$charset = mysqli_client_encoding($con);
echo "The current character set is:" . $charset; 
echo $charset;
mysqli_close($con);
?>
</body>
</html>
Advertisements
Comments
  1. sanjay says:

    great this worked

  2. abby says:

    Thank you ! you save my life ! that worked perfectly

  3. after a long search, your solution makes me very happy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s