3

Diacritic signs http://img98.imageshack.us/img98/3383/dijakritickiznakovi.gif

So, symbols belows display title should be displayed that way.
UTF-8 entities are listed below HTML (utf-8) title (here is list: LINK)
And last line shows what is stored in my database.
Collation of db table is utf8_unicode_ci.
I suppose that symbols in db shouldn't be as they are in my case? They are displaying correctly on page when loaded from database, but they all of them are not displayed by utf-8 table from given link. Even if I see them correctly maybe someone other won't?

ilija veselica
  • 9,414
  • 39
  • 93
  • 147

2 Answers2

4

Setting the MySQL table charset is not enough - you should also take care to set the correct charset for the client, the connection and the results, which defaults may differ from server to server making your database less than portable: the same database content might be displayed differently moving to another server.

I've been storing slovenian text into MySQL for some time now and this is what works for me:

  • the first thing you do after connecting should be to issue a "SET NAMES utf8" query
  • make sure that the strings you're storing are utf-8 to start with: if you're taking them from a web page form make sure the page is UTF-8
  • be careful what tools do you use to browse/edit the database contents online: PhpMysqlAdmin is definitely unsafe.

Hope this helps.

djn
  • 3,950
  • 22
  • 21
  • Could you please be more precise. I don't know WHERE exactly should I put this query? Do I put it in each query to database? I see that SET NAMES utf8 is replacement for 3 variables (character_set_client, character_set_results, character_set_connection) but I am not clear with where to put this query. Thank you for help! – ilija veselica Oct 04 '09 at 19:34
  • Just make it the first query to the database - right after you do mysql_connect($server, $user, $password); – djn Oct 05 '09 at 23:40
  • 1
    Oops, must have pushed the wrong button. What I was trying to write was: mysql_connect($server, $user, $password); mysql_select_db($dbname); mysql_query("SET NAMES utf8"); //the very first query... Doing this will allow your connections to be server/client-independent. Since I started doing it this way I can safely move databases from my Windows devel server to my Linux production box via the MySQl Migration Toolkit or copying straight db-to-db with HeidiSQL and a SSH tunnel - all witout any charset mismatch. – djn Oct 05 '09 at 23:53
  • Take note: you have to start doing SET NAMES before you send anything to the database. It won't 'repair' what is already stored there. – djn Oct 05 '09 at 23:54
3

You appear to be trying to store HTML-encoded strings in your database. Don't do that, it will only break your ability to do string operations like searching reliably. You should be able to store raw UTF-8 encoded characters as bytes in your database.

You don't say what environment you're using to read the database or how you get the ‘incorrect’ string at the bottom (which is UTF-8 bytes read using ISO-8859-1 encoding). If they appear in your web page (and you're specifying UTF-8 in the headers and/or <meta> tag), you're presumably pretty much there.

bobince
  • 528,062
  • 107
  • 651
  • 834