Inserting foreign character into mysql

Hello Guys,

I am struggling to understand or work out how to insert foreign characters such Arabic, Japanese into mysql.

I’ve tried the follwing, but none of them work.

try 1:


mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET utf8");
$string = "غير متوفرة";
$sql="Update strings set hi = '".$string."' ";
if(!$run=mysql_query($sql)){
	echo mysql_error();
}

try 2:


mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET utf8");
$string = utf8_encode("غير متوفرة");
$sql="Update strings set hi = '".$string."' ";
if(!$run=mysql_query($sql)){
	echo mysql_error();
}

look at this url http://devzone.zend.com/images/articles/4469/image2.jpg how has this guy managed to do it? Any ideas plzzz:(

bump…any ideas anyone plz

What is your field’s collation? As far as i know it should be ‘utf8_general_ci’ and your table’s charset should be ‘utf8’.

Yes they are in utf. here you see:

CREATE TABLE langs (
id char(50) character set utf8 NOT NULL,
name char(50) character set utf8 NOT NULL,
meta char(50) character set utf8 default NULL,
error_text char(50) character set utf8 NOT NULL,
encoding char(50) character set utf8 default NULL,
PRIMARY KEY (id))
ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE strings (
string_id char(100) character set utf8 NOT NULL,
page_id char(50) character set utf8 NOT NULL,
en text character set utf8 NOT NULL,
de text character set utf8 NOT NULL,
hi text character set utf8 NOT NULL,
PRIMARY KEY (string_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

when i insert from php it shows as ?? ??? in the database. I even changed the mysql my.ini to this:

[client]

port=3306

default-character-set=utf8

[mysql]

default-character-set=utf8

yet no luck

Could it be that it is inserting correctly, but whatever you are using to show the database isn’t displaying it properly?

I also have the same problem:
When I look at mysql interface in the command prompt, the characters are unreadable.

However, when I display the records on my browser, everything is in order and it works fine.

I don’t know whether mysql in the command prompt window will display non-roman scripts correctly, but it does fine when accessing it from a web interface with a up-to-date browser. Do you need to read the records in the command prompt or can you get by with relying only on viewing from a browser?

hey stormrider, when i check the db it shows ? marks

when I render on the brwowser it also shows as ? marks

swto, so how does it show in your db? are you simply echoing it out on the browser that it works fine?

this is how i render it on the browser


require_once 'Translation2.php';

// define language table parameters
$params = array(
  'langs_avail_table'     => 'langs',
  'lang_id_col'           => 'id',
  'lang_name_col'         => 'name',
  'lang_meta_col'         => 'meta',
  'lang_errmsg_col'       => 'error_text',
  'lang_encoding_col'     => 'encoding',
  
  'strings_default_table' => 'strings',
  'string_id_col'         => 'string_id',
  'string_page_id_col'    => 'page_id',
  'string_text_col'       => '%s',
);

// initialize translation engine
$tr =& Translation2::factory('MDB2', 'mysql://root:ma201dq@localhost/internationalization', $params);

// set character set
$tr->setCharset('utf8');

// set language
if (!isset($_GET['langs'])) {
  $tr->setLang('en');
} else {
  $tr->setLang($_GET['langs']);  
}

// set error handling
PEAR::setErrorHandling(PEAR_ERROR_DIE);

// get translated string in set language
echo $tr->get('how_are_you', 'basic');


this is the bit that renders it:
// get translated string in set language
echo $tr->get(‘how_are_you’, ‘basic’);

Here is a list to consider for displaying utf8 correctly:

  1. Each table must be set as utf8:
    CREATE TABLE name {

    } DEFAULT CHARACTER SET utf8;

  2. DB connection php code:
    PHP Code:

<?php 
$link = mysqli_connect('localhost', 'root', 'your password'); 
... 
if (!mysqli_set_charset($link, 'utf8')) 
{ 
$output = 'Cannot set utf8 encoding.'; 
include 'your output.php' 
exit(); 
} 
?>

This will connect you to the db with utf8, if not it will tell you so.

echo out your values like this:
PHP Code:

<?php echo htmlspecialcharacters($table_name['value'], ENT_QUOTES, 'UTF-8'); ?>

set your webpages as charset utf8 and save your code in utf8 encoding.

  1. Finally, you should also make sure the browser you are using is properly configured to read utf8.

The collation is irrelevant - Only the encoding matters. The encoding can be set in multiple places though and they all need to match up.

First: What encoding did you save your php-file in? Check in your text editor if you don’t know.

Second: On the page where you display the string, which encoding are you serving the page with? You can check this by right-clicking and selecting View Page Info.

thanks for the run down swto

kyberfabrikken spot on :slight_smile: when i right clicked on my browser it was encoding on western European, so i changed it utf8, but why does php not cater for this. Even when I make it explicit in php like below it won’t work:

echo utf8_decode($row[‘hi’]);
echo htmlspecialchars($row[‘hi’], ENT_QUOTES, ‘UTF-8’);

i just noticed I was not serving it with any encoding, hence why it did not render properly. so i placed a header(‘Content-Type: text/html; charset=UTF-8’); and it now works fine.

thanks all

Yup. When you don’t define the encoding, most things will assume iso-8859-1.

No worries - a lot of this is taken from Kevin Yank’s Book ‘Build Your Own Database-Driven Website’, chapters 3-5. You may want to check out this book.