Converting Special Characters From Database to Normal Characters

Question

I have a mysql database backup. And this backup collation is utf8-general-ci. This is a backup from vbulletin.

Here is a sentence from an article in database:

Ticaret Merkezi sizin ticaret yapmanýzý saðlayan müthiþ bir platformdur. Bu platformda internet ile baðý olmayan tüccarlar için ticaret yapma olanaðý saðlanmýþtýr. Bazý nedenlerden dolayý kendine site kuramayan tüccarlar burada kendi ürünlerini tanýtabilecek ve yine burada bir çok tüccar ile sohbet edebilecek.

Bir çok satýcý ve müþteri arasýnda bað kuracak bu sistem sayesinde ticaret daha kolaylaþacak.

my html code is like that

<html lang="tr"> 
<head> 
<meta charset="utf8"> 
</head> 
</html>

If i don't use lang='tr' and charset='utf-8'. My data turns to this

Ticaret Merkezi sizin ticaret yapmanÃ½zÃ½ saÃ°layan mÃ¼thiÃ¾ bir platformdur. Bu platformda internet ile baÃ°Ã½ olmayan tÃ¼ccarlar iÃ§in ticaret yapma olanaÃ°Ã½ saÃ°lanmÃ½Ã¾tÃ½r. BazÃ½ nedenlerden dolayÃ½ kendine site kuramayan tÃ¼ccarlar burada kendi Ã¼rÃ¼nlerini tanÃ½tabilecek ve yine burada bir Ã§ok tÃ¼ccar ile sohbet edebilecek.

Bir Ã§ok satÃ½cÃ½ ve mÃ¼Ã¾teri arasÃ½nda baÃ° kuracak bu sistem sayesinde ticaret daha kolaylaÃ¾acak.

But lang='tr' and charset='utf-8' is not converting everything.

In fact, i have no problem with this issue. But this articles was saved into database like that.

How can convert these characters ? I don't want to use str_replace.

ý => ı, ð => ğ, þ => ş etc.

My Database Connection Part

public function __construct() {
    $this->db_connection = mysql_connect($this->_server, $this->_dbuser, $this->_dbpassword) OR die ("Veritabanı Sunucusuna Bağlanılamadı!" . mysql_error() );
    mysql_query('SET NAMES utf8'); 
    mysql_query('SET CHARACTER_SET utf8'); 
    mysql_query("SET COLLATION_CONNECTION = 'utf-8'");

    mysql_select_db($this->_db) or die("Veritabanı Bulunamadı" . mysql_error() );
}

your database connection needs to be using `SET NAMES 'utf-8'` when you were saving the data i believe — DevZer0, Jul 27 '13 at 06:58
i added my database connection part. I've already used set names — cihanblog, Jul 27 '13 at 07:02

score 1 · Answer 1 · answered Jul 27 '13 at 07:01

1

I have met problems like this before. I think first you need to make sure the console or the editor you used for viewing the article which fetched from your database is using 'utf-8' format. I mean this problem may cause by the console, not by database itself.

answered Jul 27 '13 at 07:01

Ah Wen

115
8

i know about this topic. And i've already check. "file -i index.php index.php: text/x-c++; charset=utf-8" – cihanblog Jul 27 '13 at 07:13

Padmanathan J · Answer 2 · 2013-07-27T07:11:37.097

As I've worked a lot with "non-english" characters, several things are required for proper display and storage of those characters.

In no particular order (as I don't know what charset is best suited for Persian, I'll use UTF-8, if it's different, you just use the one you need):

Tell your browser what charset you are using, either by setting the proper header from PHP header('Content-type: text/html; charset=utf-8'); or set the meta tag in your html like so: <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

In the database avoid mixing different collations and charsets in the columns/tables. I always set the database, the tables and the columns to utf8_general_ci which for my needs work all the time (languages like English, German, Serbian, Hungarian...).

As Jan said, read http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html You'll most likely need to execute query something like SET NAMES utf8 right after connecting to the database.

All this should ensure the proper displaying of unicode characters. However, there is one more thing that can override all this - the web server. Apache (don't know about the other servers) has a AddDefaultCharset directive. On most setups this is left as Off, but I did came across setups where the default charset was set to latin1, thus overriding all my charset settings. If this is set, it is set in the httpd.conf (or similar configuration file). If you have access to it, I recommend setting it to Off. If you don't, then you can override the global value with .htaccess placed in your webroot, with something like: AddDefaultCharset utf-8

nothing changed. Because this data is saved to database like that when i query phpmyadmin i can see these characters ý => ı, ð => ğ, þ => ş etc. But when i open vbulletin forum. Everything is normal. There is convertion. But i don't know how can i do that ? — cihanblog, Jul 27 '13 at 07:04

Converting Special Characters From Database to Normal Characters

2 Answers2