Strange characters when reading data from mysql table

Question

Am using mysql and php.

In my database i have column value like following.

PreaqueÃ§a o forno mÃ©dio (180ÂºC).

But when i retrieve it using php it shows below string.

Preaqueu00e7a o forno mu00e9dio (180u00baC).

Something is wrong with character encoding. Any ideas to fix it?

make sure all your systems use the right encoding (probably UTF)? — Franz Gleichmann, Nov 17 '16 at 12:00
your table config is belongs to like this DEFAULT CHARSET=utf8 — Karthi, Nov 17 '16 at 12:02
$sql="SELECT * FROM tablename WHERE id='$id'"; $result=mysql_query($sql); @Karthi — jomin v george, Nov 17 '16 at 12:07
@jdoe don't post code in comments, *edit* your question instead to include additional information. also: **don't** use `mysql_` functions - they are deprecated and in PHP7.0 removed. use `mysqli_` or `PDO` instead — Franz Gleichmann, Nov 17 '16 at 12:18
If you are using mysql try mysql_query("SET NAMES utf8") after select query and if you are using mysqli mysqli_set_charset($conn,"utf8") and If you are using class and object of mysqli then use below $mysqli = new mysqli("localhost", "root", "", "test"); $mysqli->set_charset("utf8"); — Jay Gosai, Nov 17 '16 at 12:20
See "Mojibake" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored — Rick James, Nov 17 '16 at 23:08

score 0 · Answer 1 · edited May 23 '17 at 12:07

You seem to have two problems.

Ã§ is Mojibake for ç and Âº is Mojibake for º. For that, you need to check:

visible.)

The bytes to be stored need to be UTF-8-encoded. Fix this.
The connection when INSERTing and SELECTing text needs to specify utf8 or utf8mb4. Fix this.
The column needs to be declared CHARACTER SET utf8 (or utf8mb4). Fix this.
HTML should start with .

Hex 00e7 looks like the "Unicode codepoint" for ç, but it is unclear how you could have created that.

Let's start by doing SELECT col, HEX(col) FROM ... to see what got stored. Also provide SHOW CREATE TABLE. (More details in Test the data .)

After fixing the Mojibake issues and doing the HEX, start a new question. (I fear that this thread will get too messy to keep.)

score 0 · Accepted Answer · answered Nov 18 '16 at 06:22

I was using json_encode to display the output from select query. That was causing the problem.

function my_json_encode($arr)
{
        //convmap since 0x80 char codes so it takes all multibyte codes (above ASCII 127). So such characters are being "hidden" from normal json_encoding
        array_walk_recursive($arr, function (&$item, $key) { if (is_string($item)) $item = mb_encode_numericentity($item, array (0x80, 0xffff, 0, 0xffff), 'UTF-8'); });
        return mb_decode_numericentity(json_encode($arr), array (0x80, 0xffff, 0, 0xffff), 'UTF-8');

}

Used this function instead of the default json_encode.And it works perfectly.

Strange characters when reading data from mysql table

2 Answers2