Reading Unicode characters from MySQL with PHP

Question

I've inherited a MySQL database which contains a field named Description of type text and collation of latin1_swedish_ci.

The problem with this field is it contains utf-8 data with some Unicode characters, e.g. character 733, etc. Sometimes this character also exists in the field represented as HTML encoded "&#733" as well.

I'm trying to read the table and export the data to a CSV file and I need to represent this character as a double quote.

Reading the HTML encoded character is easy enough. However, it appears that the actual Unicode character is converted to utf-8 before I can do anything with it resulting in a "?".

How do I read in the Unicode character 733 (U+02DD), recognize it and convert it?

Here's a simplified (not tested) version of the code.

<?
$testconn=odbc_connect ("TESTLIB", "......", "......");

$query="SELECT Description FROM TestTable";

$rsWeb=mysql_query($query));

$WebRow=mysql_fetch_row($rsWeb));
$Desc = $WebRow[0];
$Desc = str_replace('"','""',$Desc);

fwrite($output,"\"".$Desc."\",\r\n");
%>

I tried html_entity_decode(). However, the character has already been converted to a "?" before I get the chance to use html_entity_decode making it useless. It looks like it's converted to a "?" during either the mysql_fetch_row or mysql_query. — user1152124
– user1152124, Commented Jan 16, 2012 at 15:33

Pali Jasem Jr. · Accepted Answer · 2017-01-22 19:46:19Z

2

Also set charset to utf-8 when connecting to SQL server:

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

$mysqli->set_charset("utf8");

answered Jan 22, 2017 at 19:46

Pali Jasem Jr.

1746 bronze badges

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

Comments

Somal Somalski · Accepted Answer · 2012-01-16 15:29:52Z

0

I think your connection charset is not utf8, that's why chars are being converted to '?'.

Read this: http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html

Post result for query: show variables like 'char%';

answered Jan 16, 2012 at 15:29

Somal Somalski

5781 gold badge7 silver badges21 bronze badges

Comments

Wander Nauta · Accepted Answer · 2012-01-16 15:32:44Z

0

You really should put only non-entity (Unicode) version in the database, and entity-decode the rest. However, when you want to use UTF-8 with MySQL, there are a few things to remember:

Your table column's collation should be utf8_bin or similar.
Your table's collation and database collation should also be utf8_bin just in case.
Your connection charset should be UTF8. Do this by executing the "SET NAMES utf8" query.

Also, if you're outputting a HTML page, that should have the UTF8 charset as well. If everything is correct, the UTF8 characters should come out fine.

Good luck!

answered Jan 16, 2012 at 15:32

Wander Nauta

19.7k1 gold badge50 silver badges65 bronze badges

Collectives™ on Stack Overflow

Reading Unicode characters from MySQL with PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related