How to properly display non-redable characters stored in a MySQL Database

Question

I have São Tomé and Príncipe stored in a MySQL database.

It displays on my web page as S�o Tom� and Pr�ncipe

Is there anyway to display it properly straight from the database?

My MySQL Type is MyISAM and the collation is utf8_general_ci

My web page charset is <meta charset="utf-8">

Field type is set to varchar(30) and field collations is same as table utf8_general_ci

PHP FUNCTION TESTS

utf8_decode() converted characters to S?o Tom? and Pr?ncipe htmlspecialchars_decode() converted characters to S�o Tom� and Pr�ncipe html_entity_decode() converted characters to S�o Tom� and Pr�ncipe quoted_printable_decode() converted characters to S�o Tom� and Pr�ncipe htmlentities() returned blank result

GOT IT TO WORK THANKS TO @SAEVEN

Ran this right before my query SELECT

mysqli_query($GLOBALS['db_link'], "SET NAMES 'utf8'");

See also [The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets](http://www.joelonsoftware.com/articles/Unicode.html) — mario, Mar 22 '13 at 02:21
@H.Ferrence thanks, I saw it just now. Only other thing that comes to mind is the infamous/notorious "BOM". — Funk Forty Niner, Mar 22 '13 at 02:24
Is/are your file(s) set `with` or `without` the BOM? I've answered quite a few questions relative to yours. — Funk Forty Niner, Mar 22 '13 at 02:27
It's the `byte order mark` see the Wiki on the subject > http://en.wikipedia.org/wiki/Byte_order_mark - I have a client's website that just hates not having it. — Funk Forty Niner, Mar 22 '13 at 02:29
Yeah, I just googled it. how do I detect the byte order mark? @Fred — H. Ferrence, Mar 22 '13 at 02:30
Get a copy of Notepad++ > http://notepad-plus-plus.org/ - open the file in question then under `Encoding`, it will show you what it is set at. You then go to `Encoding` again, then either convert to `UTF-8 without BOM` or `Convert to UTF-8`. One of which may solve your problem. It has for me, many times. — Funk Forty Niner, Mar 22 '13 at 02:31
Ok, maybe I'll get a copy of notepad++ someday. Seems like a lot of work to get to displaying the data properly -- I mean, downloading/installing and trying to learn note++. For now, I guess my issue is not that critical to solve. Thanks for helping @Fred. — H. Ferrence, Mar 22 '13 at 02:34
You're welcome, worth a shot though. Have a look at this on SO, might be of help > http://stackoverflow.com/questions/2052982/accented-characters-stored-in-mysql-database?rq=1 — Funk Forty Niner, Mar 22 '13 at 02:35
Well, I have many non-readable chars in that table. I was hoping to find some php decode function or something like that. I'll just live with the issue for now. Thanks again @Fred — H. Ferrence, Mar 22 '13 at 02:36
Have you consulted the PHP manual on the subject? > http://php.net/manual/en/function.utf8-encode.php — Funk Forty Niner, Mar 22 '13 at 02:38
I goggled decoding...then came here. The php manual is not always easy to use to get solutions. I know there are a few encode() and decode() functions. I was hoping someone might have had this issue and solved it. I am going to to go the link you provided above and see if that helps me. Thanks again @Fred — H. Ferrence, Mar 22 '13 at 02:40
Anytime @H.Ferrence - sorry I couldn't be of more help. That's about the scope of my knowledge (for now). I'll give it an `upvote`, see if anyone else picks up on it. Cheers — Funk Forty Niner, Mar 22 '13 at 02:41

score 2 · Accepted Answer · answered Mar 22 '13 at 02:50

Could it be that you need to specify your namespace collation at the connection level?

I assume that you've covered the basics, you've got the charset as utf8 in your HTML markup.

Try executing this SQL statement before you do any of your database queries:

'SET NAMES utf8'

e.g., if you are using PDO

// do your regular connection logic whatever it is
$dbc = new PDO( .... );
$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbc->query( 'SET NAMES utf8' );

// run your queries beneath
....

or can use the shorthand

new PDO(
    'mysql:host=mysql.example.com;dbname=awesome_db',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);

Whatever DBAL you use, that query'll work. Just run it on the same connection you use to pull your data, before pulling it (only needs to be done once)

thanks @saeven - you helped me solve my issue. I amended my question with the solution I used (I code procedurally) — H. Ferrence, Mar 22 '13 at 03:09
Glad I could help. Common problem that can be pretty obscure unless you've banged your head against a wall over it before. ;) Thanks for the vote! — Saeven, Mar 22 '13 at 03:10

How to properly display non-redable characters stored in a MySQL Database

1 Answers1