PHP - UTF8 Decoding Issue

Question

I am having problems decoding UTF8 characters in my script from my sql.

Lets say I have two characters coming from mysql:

'á' & ❤️

with my script á is decoded fine however, the emoticon is decoded in â¤ï

What am I doing wrong?

$conn = new mysqli($servername, $username, $password, $dbname);
$sql="SELECT * FROM `community` ORDER BY `community`.`date` DESC LIMIT 25";

mysqli_set_charset($conn, "utf8"); //UTF8

$result = $conn->query($sql); 

while($row = $result->fetch_assoc()) {

    $comment = $row['comment'];

    echo $comment . "</br>";

    //echo htmlentities($comment); not working... white screen


}

UPDATE

I have changed Database and Tables

$conn = new mysqli($servername, $username, $password, $dbname);
$sql="SELECT * FROM `community` ORDER BY `community`.`date` DESC LIMIT 25";

mysqli_set_charset($conn, "utf8"); //UTF8

$result = $conn->query($sql); 

while($row = $result->fetch_assoc()) {

    $comment = $row['comment'];
    $comment = mb_convert_encoding($comment, "UTF-8");

    echo $comment . "</br>";

    //echo htmlentities($comment); not working... white screen


}

Also see http://stackoverflow.com/q/279170/476 – deceze Feb 26 '16 at 14:30 — deceze, Feb 26 '16 at 14:30

Asur · Answer 1 · 2016-02-26T14:27:12.827

0

Your issue here is probably with the database. You need to set your database charset to UTF-8 in order to query it correctly, what you are doing is to get a string and setting Client side default charset using

mysqli_set_charset($conn, "utf8"); //UTF8

That's not enough, so I would recommend you to run an SQL query like

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

in order to update it. If what you need is to change a single table use

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Finally you can check it using

SELECT DEFAULT_COLLATION_NAME FROM data_dictionary.SCHEMAS WHERE     SCHEMA_NAME = 'databasename' LIMIT 1;

As an extra appointment and just in case in PHP you can convert the encoding of a string using

$comment = mb_convert_encoding($comment, "UTF-8");  //Change encoding  
echo mb_detect_encoding($str, "auto"); // Check encoding

Of course you should make a backup before making any of these changes, just in case.

EDIT: The proper order to run these queries, is:

Run it in the whole DB using the first query
Run it table by table using the second query
Check if the charset has been set correctly using the third query

EDIT 2 : Remember to set the tag <meta charset="UTF-8"> in your html file.

I hope this helps you :)

edited Feb 26 '16 at 14:27

answered Feb 26 '16 at 14:10

Asur

379
3
20

Thank you. I have tried to change the `Collocation` to 'utf8_unicode_ci' but still have the same issue – SNos Feb 26 '16 at 14:15
@SNos that's because you need to change it first at the database and then in each table. I will edit the answer – Asur Feb 26 '16 at 14:16
I have tried changing as you suggested, I still cannot see the emoticons. In the database they are stored like: `â¤ï` and not converted by php – SNos Feb 26 '16 at 14:26
@SNos Ok we are getting closer, check new updates – Asur Feb 26 '16 at 14:27
Now I get `?` instead of `á` and `â¤ï` still did not converted – SNos Feb 26 '16 at 14:38
@SNos Update your post with the results of the methods and queries I've given you to have more information – Asur Feb 26 '16 at 14:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104657/discussion-between-asur-and-snos). – Asur Feb 26 '16 at 15:43

PHP - UTF8 Decoding Issue

1 Answers1