Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    UTF-8 causing trouble :(

    Hi everyone,

    I need some help with the following issue :
    The accentuated characters of the content taken form the UTF-8 database gets garbled once displayed on my website.

    What I don't understand is that everything seems to be properly set :

    my.ini
    Code:
    [mysql]
    default-character-set = utf8
    default-character-set = utf8
    character-set-server = utf8
    collation-server = utf8_general_ci
    init_connect = 'SET collation_connection = utf8_general_ci'
    init_connect = 'SET NAMES utf8'
    PHP header
    Code:
    header("Content-Type:text/html; charset=utf-8");
    Metas
    Code:
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    Table structure
    Code:
    CREATE TABLE `mod_geonames` (
      `geoname_id` mediumint(9) NOT NULL,
      `name` varchar(200) default NULL,
      `latitude` decimal(10,7) default NULL,
      `longitude` decimal(10,7) default NULL,
      `country_code` varchar(2) default NULL,
      PRIMARY KEY  (`geoname_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    So, what else could cause the problem?
    I am at a total loss of ideas after extensive googling

    Thanks for the help!
    Last edited by sleidia; 02-28-2009 at 05:16 PM.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by sleidia View Post
    So, what else could cause the problem?
    probably the way you display the page. what charset you use in the page with this problem?

    best regards

  • #3
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks so much for being here

    The site being totally dynamic, all the pages use the same header as shown above , in UTF-8.

    Out of desperation, I've tried one other thing :
    I made a sql dump of the database and then pasted it in a UTF-8 sql file on pspad, then saved it and imported it again into the database. The result was that everything was displayed properly in the website this time ... but all is garbled when viewing the data in SQLyog

    Note that I'm working locally on appserv/windows vista.

    Any help will be highly appreciated

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by sleidia View Post
    Thanks so much for being here

    The site being totally dynamic, all the pages use the same header as shown above , in UTF-8.

    Out of desperation, I've tried one other thing :
    I made a sql dump of the database and then pasted it in a UTF-8 sql file on pspad, then saved it and imported it again into the database. The result was that everything was displayed properly in the website this time ... but all is garbled when viewing the data in SQLyog

    Note that I'm working locally on appserv/windows vista.

    Any help will be highly appreciated
    post a link to a page with this problem if you can. I still think that is not a mysql problem. Maybe I could find something,

    best regards

  • #5
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ahhhh sorry ... i can't show anything online

    I think the problem must come from the database because my site use UTF-8 encoded flat text files for some areas and they are displayed correctly.

    Only the data coming from the dabase is broken.

    What I don't understand is that one table has been downloaded from www.geonames.org so I'm sure it's properly encoded.

    Thanks again!

  • #6
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm starting to think that the site is OK, the database is OK too but, for some reason, the database content (utf) is sent as latin1 to the website.

    But I have no way to prove/fix that

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by sleidia View Post
    ahhhh sorry ... i can't show anything online
    no problem. I ask for a link with hope that I can discover something if can see the server response.

    I think the problem must come from the database because my site use UTF-8 encoded flat text files for some areas and they are displayed correctly.

    Only the data coming from the dabase is broken.

    What I don't understand is that one table has been downloaded from www.geonames.org so I'm sure it's properly encoded.

    Thanks again!
    some ideas:
    1. try a "show create table mod_geonames" from a mysql client if you have one and check if charset is utf-8, as you posted in #1
    2. try to see the response headers for that page using web developer extension for firefox or in firebug -> net
    3. check the in you browser what setings have for encoding. For example in firefox could be auto-detect off and encoding iso-8859-1. In case is not utf-8, force it to be utf-8 and see if get rid of the problem.

    Unfortunately I can't help with something specific for vista since I don't use windows.

    best regards

  • Users who have thanked oesxyl for this post:

    sleidia (02-28-2009)

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by sleidia View Post
    I'm starting to think that the site is OK, the database is OK too but, for some reason, the database content (utf) is sent as latin1 to the website.

    But I have no way to prove/fix that
    try 3. and 2 from my previous post.

    best regards

  • #9
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oesxyl View Post
    1. try a "show create table mod_geonames" from a mysql client if you have one and check if charset is utf-8, as you posted in #1
    Did it and looks OK :

    Code:
    CREATE TABLE `mod_geonames` (
      `geoname_id` mediumint(9) NOT NULL,
      `name` varchar(200) default NULL,
      `latitude` decimal(10,7) default NULL,
      `longitude` decimal(10,7) default NULL,
      `country_code` varchar(2) default NULL,
      PRIMARY KEY  (`geoname_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    Quote Originally Posted by oesxyl View Post
    2. try to see the response headers for that page using web developer extension for firefox or in firebug -> net
    Did that too :

    Code:
    Date: Sat, 28 Feb 2009 18:20:47 GMT
    Server: Apache/2.2.8 (Win32) PHP/5.2.6
    X-Powered-By: PHP/5.2.6
    Expires: Mon, 26 Jul 1997 05:00:00 GMT
    Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
    Pragma: no-cache
    Last-Modified: Sat, 28 Feb 2009 18:20:47 GMT
    Keep-Alive: timeout=5, max=100
    Connection: Keep-Alive
    Transfer-Encoding: chunked
    Content-Type: text/html; charset=utf-8
    
    200 OK
    Quote Originally Posted by oesxyl View Post
    3. check the in you browser what setings have for encoding. For example in firefox could be auto-detect off and encoding iso-8859-1. In case is not utf-8, force it to be utf-8 and see if get rid of the problem.
    This too is OK. Unchecked auto-detect but still the same. UTF-8 is selected.
    Checked on multiple browsers.

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    only thing I have in mind in this case is that data from database is broken. Can you check some way if what you get from data base is what you expect?

    best regards

  • #11
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    What is totally weird is that Latin1 and UTF-8 are supposed to be the same for simple characters, right? It's not like I'm working with Japanse or Chinese.
    I only use latin characters like etc... but they get replaced by question marks.

    I don't get it.

    As for your last suggestion, I don't know where/how to look at.

    Anyway, I want to thank you again for your help.

  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by sleidia View Post
    What is totally weird is that Latin1 and UTF-8 are supposed to be the same for simple characters, right? It's not like I'm working with Japanse or Chinese.
    I only use latin characters like etc... but they get replaced by question marks.

    I don't get it.

    As for your last suggestion, I don't know where/how to look at.

    Anyway, I want to thank you again for your help.
    try to convert, using a server side scripting language, what you get from database from latin-1 to utf-8 and see if this will fix the problem.

    best regards

  • #13
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Okay, some great guy on a different forum gave the solution.

    Actually, I just had to insert
    mysql_query("SET NAMES 'UTF8'");
    just after the connexion to the database in the PHP code.

    I hope it will help others.

    Thanks anyway for the help you provided.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •