Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

php mysql and chinese

Posted on 2014-03-10
12
Medium Priority
?
699 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 111

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

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 111

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

972 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