Solved

php mysql and chinese

Posted on 2014-03-10
12
682 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 58

Expert Comment

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

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 110

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 110

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 110

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 110

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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

623 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