Page 1 of 1
Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 1:09 am
by HarPaddy
Hi,
I have some problem with special characters in my pages which I am using PHP and MySQL.
When we insert data with some Catalan special characters (à é è í ï ó ò ú ü ç) that data is storing in data base like below
>Actual content: Bar restaurant La Cuspinedaú
>Database storing content: Bar restaurant La Cuspinedaú
If We execute direct query on PHPMyadmin area characters are inserting in MySql database while executing query through mysql_query command on php its giving like this.
Is this PHP or MySQL configuration settings problem?
I am struggling from two days,Please help me regarding this.
Thanks in advance
- Har Paddy
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 3:49 am
by mickeyunderscore
This is a common problem and often happens with pound signs and ampersands. Try using the PHP function htmlentities() before inserting your data into the database:
http://uk2.php.net/manual/en/function.htmlentities.php
Example: htmlentities(é, ENT_QUOTES, 'UTF-8');
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 4:03 am
by Apollo
Using htmlentities here is just a workaround and doesn't solve the actual problem.
HarPaddy, your database is probably using a different encoding than your website or wherever the data comes from. How are the original strings encoded, and what encoding does your database use?
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 4:16 am
by HarPaddy
Hi Appollo,
Thanks for the response.
We are using the server as
MySQL charset: UTF-8 Unicode (utf8) And the collation is utf8_unicode_ci.
And for Database: while creating Database we had given like this
DEFAULT CHARACTER SET utf-8 COLLATE utf8_general_ci;
We had moved the total content(files and DB) from one server to another, in the old server for the same programming the special characters are saving in Mysql database as it as, But for new server only we are facing this problem..
-HarPaddy
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 5:38 am
by Apollo
If "ú" is stored as "ú", then it seems you are correctly storing them as UTF-8 strings, but displaying it as some ansi encoding (most likely iso-8859-1 or windows-1252).
When you say "Database storing content: Bar restaurant La Cuspinedaú", exactly what do you mean? When do you see that or how do you verify this? If you output data from the database on a webpage, do you specify utf-8 encoding in the webpage's meta header?
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 7:23 am
by HarPaddy
Hi Apollo,
Thanks for your Reply.
While displaying the content we are giving meta charset as 'iso-8859-1'.
After moving the content (files and DB) from old server to new server i am getting this problem.
My main problem is while saving the data with catalan special characters in database itself the special characters are storing like this "ú".
In my old server the special characters are saving like this "ú".
May i know which server type(PHP configaration and Mysql ) settings is causing like this.
The files or pages which i am using for inserting into DB and displaying on browser are same in both old and new servers.
Thanks
-Paddy
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 8:28 am
by Apollo
HarPaddy wrote:While displaying the content we are giving meta charset as 'iso-8859-1'.
Can you try to display it using 'utf-8' instead?
My main problem is while saving the data with catalan special characters in database itself the special characters are storing like this "ú".
In my old server the special characters are saving like this "ú".
The problem is: in what encoding? Can you give the exact binary representation?
The "ú" character (u with acute accent) is stored in iso-8859-1 and windows-1252 as 0xFA (one byte), and in utf-8 as 0xC3 0xBA (two bytes).
If you accidentally display the utf-8 representation (i.e. 0xC3 0xBA) in a page using iso-8859-1 encoding, you'll see "ú".
Vice versa, if you accidentally display the iso-8859-1 representation (i.e. 0xFA) in a page using utf-8 encoding, you'll probably see "

" because that's not a valid sequence in utf-8.
May i know which server type(PHP configaration and Mysql ) settings is causing like this.
It's really a combination of factors: either the data is stored incorrectly in the database (in a different encoding than you expect), or the database is configured to extract strings in iso-8859-1 format (whereas you expect utf-8), or the data is incorrectly displayed in iso-8859-1 encoding while it's really utf-8.
The latter seems the most likely, hence the suggestion above (try utf-8 charset meta tag).
If changing the charset of the displaying page to utf-8 does not help, what does this output:
Code: Select all
$s = (...get string with fancy character from your database...);
print(bin2hex($s));
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Tue Feb 03, 2009 11:23 pm
by HarPaddy
Hi Apollo,
Thanks for you reply.
I had tried with 'utf-8' in meta tag but same output only displaying.
and i had checked this code on the server.
$s = "Granja Nórdica";
print(bin2hex($s));
the result is : " 4772616e6a61204ef37264696361 ";
Mysql DB field is saving the above string as " Granja Nórdica "
charset of DB is 'utf-8' and collation is 'utf_general_ci' the field collation is 'latin1_swedish_ci'.
Re: Special charaters problem in MySQL-- PLease Help Me
Posted: Wed Feb 04, 2009 3:41 am
by Apollo
HarPaddy wrote:and i had checked this code on the server.
$s = "Granja Nórdica";
print(bin2hex($s));
the result is : " 4772616e6a61204ef37264696361 ";
But now you're putting the string directly in your php source code, instead of getting it from your database.
If you would print $s, then I guess it will look OK if your page has an iso-8859-1 charset metatag, and it would probably print "Granja N

rdica" if the page has an utf-8 charset metatag.
Mysql DB field is saving the above string as " Granja Nórdica "
charset of DB is 'utf-8' and collation is 'utf_general_ci' the field collation is 'latin1_swedish_ci'.
There's a strange discrepancy (why not the same encoding everywhere?).
In the example code above, instead of putting the string literally in your code, extract it from your DB. Something like this:
Code: Select all
<?php
print('<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body>');
mysql_connect( 'host', 'username', 'password' );
mysql_select_db( 'yourdatabase' );
// mysql_query('SET NAMES utf8'); // optional, keep this disabled first
$res = mysql_query("SELECT name FROM yourtable WHERE name LIKE 'Granja%' LIMIT 1");
if (!mysql_num_rows($res)) die('name not found');
$row = mysql_fetch_row($res);
$s = $row[0];
print(bin2hex($s).'<br>'.$s);
print('</body></html>');
?>
What does this output?
Actually, can you try
four things: the code above, the code above but with charset=iso-8859-1 in the metatag instead of utf-8, and those same two again with the "SET NAMES utf8" query uncommented. What results do you get?