Solved

php mysql and chinese

Posted on 2014-03-10
12
669 Views
Last Modified: 2014-03-11
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
0
Comment
Question by:cycledude
  • 5
  • 5
  • 2
12 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39918312
How are you connecting to the database? What driver?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39918370
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
 

Author Comment

by:cycledude
ID: 39918415
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 58

Expert Comment

by:Gary
ID: 39918437
What header are you sending with your code?
Are your php pages saved as UTF-8 (not ANSI)
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39918460
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
 

Author Comment

by:cycledude
ID: 39920010
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
 

Author Comment

by:cycledude
ID: 39920395
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
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39920469
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
 

Author Comment

by:cycledude
ID: 39920540
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
 

Author Closing Comment

by:cycledude
ID: 39920548
the way to go was to add

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

to the constructor.

thanks ;o)
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39920554
Design unique modern style villa, you can see the beautiful sea, with swimming pool, we can immediately enjoy the wonderful life.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39920563
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I setup Wordpress to use Master / Slave MySQL database setup? 4 61
Link failure 16 37
asp Google Map 2 33
Reference key in foreach loop 4 22
Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question