inserting unicode characters into mysql database shows ??????? in field

Question

Hey guys I am having trouble trying to convert my web app to support unicode characters. I have the following script which tries to insert russian characters into my mysql database but just outputs ?????? in my mysql database field. I have changed default charset to UTF-8 in my php.ini and have modified my table fields to collation: utf8_unicode_ci. Anyone have any ideas?

mb_language('uni'); mb_internal_encoding('UTF-8');

                $sql = 'SET NAMES utf8';
$stmt = $conn->prepare($sql);
$result=$stmt->execute();

$sql = 'SET CHARACTER SET utf8';
$stmt = $conn->prepare($sql);
$result=$stmt->execute();

$sql = 'INSERT INTO topic (topic_id,topic_title) VALUES (?,?)';
$stmt6 = $conn->prepare($sql);
$result=$stmt6->execute(array(0,"дравствуйте"));
?>

show create table edit

CREATE TABLE `topic` (
 `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `topic_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

 `description` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

 PRIMARY KEY (`id`),
 FULLTEXT KEY `description` (`description`),
 FULLTEXT KEY `topic_title` (`topic_title`),
 FULLTEXT KEY `topic_title_2` (`topic_title`,`description`),
 FULLTEXT KEY `description_2` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=latin1

Answer 1

Normally, (if this you're quoting file is also in utf-8) the PHP & MySQL side is OK, but the HTTP / HTML side of things isn't.

• Do you send a Content-Type: text/html;charset=utf-8 header?
• Is there a <meta> element on the HTML page that claims another charset then utf-8?

---

So, after more infomation: in 'normal' PHP scripts it works I gather (?), in PHPMyAdmin it doesn't (which should make it more of a candidate for superuser.com b.t.w.). A version of PHPMyAdmin used would be nice, but normally, a $cfg['DefaultCharset'] = 'utf-8'; in PHPMyAdmin config should work if the rest of non-PHPMyAdmin script works. If not: update to the latest PHPMyAdmin release, and if it still doesn't work file a bug report.

Answer 2

If you are seeing those ?????? on mysql browser or on mysql cli, make sure you tune up the charset settings on the programs, even if the entries are correctly added to the database, displaying them correctly also depends on the client charset settings.

Answer 3

I'm inclined to think the problem isn't with MySQL.

You are entering an non ASCII string as a literal into a PHP script. Are you sure the "дравствуйте" string will be sent to MySQL server as UTF-8 encoded?

I've seen some problems with nos ASCII characters with PHP and MySQL with incompatible charset configurations amongst the operating system, the PHP interpreter (it inherits the default from the operating system), the database and the development tools (the encoding of .php files). If all four are configured to the same encoding, it should work.

You said that your php.ini is configured to UTF-8. If your source php file isn't in UTF-8, it will not work! So, check if your php files are encoded as UTF-8 and if the mysql client is configured to UTF-8 too.

A common mistake is to forget to configure the MySQL client to UTF-8 too and fail to check the data on the database because of charset problems with MySQL client application.

Home it helps!

Answer 4

I got it to work by using the following sql alter database mydatabase charset=utf8;