[RESOLVED] Problem in retrieving data from UTF-8 encoded mysql database

simongcc

Hihi!

My company website is a multiple languages website including, Chinese Traditional, Chinese Simplified, Japanese and English.

I am using mysql 5 and php 5 for running the server serving the dynamic content. The problem is:

The data inside have Chinese and English. So the DB is defined as UTF-8.
And it display in the mySQL administration tools without any problem.
And it display normally in phpMyAdmin(web browser) too.

But when I try to retrieve the data with my own code. The data become "??????". (Question Marks)
I use something like this to read the data in direct:

//do something in direct for testing purpose:
        $query = "select n.id,n.dateofissue,n.type,n.content1,n.content2,n.content3,n.url1,n.url2,n.url3,n.action,n.dateofregister,n.dateoflastchange,n.status,n.staffid,n.title,n.typeoflink,n.typeofnews,u.fullname,n.enable1,n.enable2,n.enable3,n.country from product_whatsnews n,userinfo u where n.staffid=u.id and n.type=1";

    $result = $db->query($query);
    $num_results = $result->num_rows;

    while ($row = $result->fetch_row()) {
        printf ("%s (%s)\n", $row[0], $row[3]);
      echo mb_check_encoding($row[3])." ".mb_detect_encoding($row[3])."<br>";
    }; //output is all 1  +  some are Ascii while some are UTF-8

    //$row = $result->fetch_row();
    foreach ($save_row as $key => $value) {
        echo $key." ".$value."<br>";
    }

Is there something I missed out?
I have tried also "mb_convert_encoding" but it does not work also.

I have tried many ways for about a month so I think I better ask a profession for an advice. Because my php experience or programming experience is not deep enough.

My problem is similar to this thread but the thread seems to be unresolved.
http://phpbuilder.com/board/showthread.php?p=10856616#post10856616

Thanks for taking time to read.

NogDog

Start by making sure the [X]HTML you are outputting is using the same character set as your data, i.e. UTF-8. To make sure, I would use both a content-type header (in case your server defaults to something else) and meta tag:

<?php
header('Content-Type: text/html; charset=utf-8');
?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang='en'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>
. . . rest of page . . .

simongcc

Thanks for your reply.
I have tried your method but it does not work.
And I try to exam the coding piece by piece.

I find the reason that cause the problem.

It is related to mysql connection character set.

When database is connected like:
$openDB = new mysqli('server name', 'user name', $global_password, 'password');
There is default encoding. It is latin1 in my case.

Using this can test what is the character set:
$openDB ->character_set_name(); //latin1 in my case

And I try to set it to UTF8 by using:
$openDB ->set_charset("utf8");

if using the $openDB ->character_set_name(); again, it shows UTF8.

And then retrieve data again, the data shows up correctly without any problem.

I can only do this simple solution. I believe further coding such as testing the charset before changing it, testing the collation and so on.
To my understandings, phpMyAdmin is writing a good encoding detection/changing system so it display information smoothly.

Hope this piece of information can help those who get the similar problem.

Thanks a lot!