Solved

php mysql and chinese

Posted on 2014-03-10
12
654 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
Comment Utility
How are you connecting to the database? What driver?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 58

Expert Comment

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

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:cycledude
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
the way to go was to add

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

to the constructor.

thanks ;o)
0
 
LVL 108

Expert Comment

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

Expert Comment

by:Ray Paseur
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now