php mysql and chinese

Hi

I need to store and display some chinese text from a mysql database...

here is an example

http://your-website.co.uk/test/chinese/

top text is the output from the database, bottom text is what the top text should be.

I have the files utf-8 encoded and I made sure that the tables in the database were too...

any ideas how I can get the database text to display correctly?

Cheers

CD
cycledudeAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
I see you have meta charset in the HTML document now.  That's probably a good thing.

Please check the article here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html

Look for the part labeled Character Sets in MySQL.  I believe that you must explicitly tell the MySQLi object to use UTF-8 MB4 encoding.  Maybe something like this in Class Database:

public function __construct() 
{
    $this->mysql = new mysqli(DB_SERVER, DB_USER,DB_PASS,DB_NAME) 
        or trigger_error($this->mysql->connect_error, E_USER_ERROR);
    $this->mysql->set_charset("utf8mb4");
}

Open in new window

0
 
GaryCommented:
How are you connecting to the database? What driver?
0
 
Ray PaseurCommented:
Please read this article about character set collisions.  The data should be correct if the entire path - input, PHP, SQL, and browser are all told to use UTF-8.  Your PHP version may be in play.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html

See the parts about Character Sets in MySQL and Using UTF-8 with PDO

If you have any questions about MySQLi or PDO, this article may be helpful:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cycledudeAuthor Commented:
connecting using mysqli

thanks ray, that's a lot of reading!

if I login to the database using phpmyadmin, tha database shows the text correctly, so I am not sure why its not displaying when I connect through my sample page.

I will have a read..
0
 
GaryCommented:
What header are you sending with your code?
Are your php pages saved as UTF-8 (not ANSI)
0
 
Ray PaseurCommented:
In the WWW link above, I did not see meta charset so you might try adding that.  Also, if you can show us the PHP that generated that page, there may be a clue in the script.
0
 
cycledudeAuthor Commented:
Hi

I figured that being able to display some chinese on the page would take out the possibility of being encoding as a problem.. but anyway heres the code:



Database class

class Database {
  
  public $mysql;

  function __construct() {
    $this->mysql = new mysqli(DB_SERVER, DB_USER,DB_PASS,DB_NAME) or die('There was a problem connecting to the db');
	
	
  }

  function query($sql) {

    if(($result = $this->mysql->query($sql)) != NULL) {
		
		if ($result->num_rows>0)
		{
			while($row = $result->fetch_object()) {
				$x[] = $row;
			}	
			return $x;
		}
		
    }
    else {
      echo $this->mysql->error;
    }
    
  }
  
}

Open in new window



and now the page code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<style>
			body { font: 10pt arial, sans-serif; }
		</style>
	</head>

	<body >
		<?php 
			
			include("db.class.php");
			$db = new Database();
			$sql = "SELECT * FROM content ORDER BY display";
			$pages = $db->query($sql);	
		?>
		
		<p><?php echo $pages[0]->description; ?></p>
		<br/><br/><br/><br/><br/><br/>
		<p>¿¿¿¿¿¿¿¿¿¿,¿¿¿¿¿¿¿¿¿,¿¿¿¿¿,¿¿¿¿¿¿¿¿¿¿¿¿¿</p>
		
</html>

Open in new window

0
 
cycledudeAuthor Commented:
ironic that the code section on this site cant display the chinese either!

all of the files have been encoded utf-8, even the table definition mysql is encoded utf8


DROP TABLE IF EXISTS `content`;
CREATE TABLE IF NOT EXISTS `content` (
  `contentid` int(11) NOT NULL auto_increment,
  `contentname` varchar(200) NOT NULL,
  `file` varchar(200) NOT NULL,
  `parentpageid` int(11) NOT NULL,
  `parentprojectid` int(11) NOT NULL,
  `backgroundtype` int(11) NOT NULL,
  `description` text NOT NULL,
  `display` int(11) NOT NULL,
  `tags` varchar(200) NOT NULL,
  `categoryid` int(11) NOT NULL,
  `bgimage` varchar(200) NOT NULL,
  `heading1` varchar(250) default NULL,
  `heading2` varchar(250) default NULL,
  `heading3` varchar(250) default NULL,
  `link` varchar(100) default NULL,
  PRIMARY KEY  (`contentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Open in new window

0
 
cycledudeAuthor Commented:
Hi, thanks ray

I added this code to the constructor

$this->mysql->set_charset("utf8");

(http://uk1.php.net/mysqli_set_charset)

and it has had an affect on the page output, it's not all ????? now... its still garbage text but its a step closer?
0
 
cycledudeAuthor Commented:
the way to go was to add

$this->mysql->set_charset("utf8");

to the constructor.

thanks ;o)
0
 
Ray PaseurCommented:
Design unique modern style villa, you can see the beautiful sea, with swimming pool, we can immediately enjoy the wonderful life.
0
 
Ray PaseurCommented:
You may get away with "utf8" but full UTF-8 support requires "utf8mb4" (thanks a lot MySQL).

http://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html

Anyway, glad you've got it working.  Thanks for the points and thanks for using EE, ~Ray
0
All Courses

From novice to tech pro — start learning today.