SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: MySQL Charset

  1. #1
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Charset

    Hi guys

    I'm using a script that collaborates rss feeds from various urls and inserts them into my database and I seem to be having a problems with certain characters IE a mid point shows up as ·

    I've traced out the data before inserting into the database and it looks fine so I think there must be a problem with my database/table charset?

    How do I change this?

    In my mysql ini file I found this setting;

    PHP Code:
    default-character-set=latin1 
    Do I change this to UTF or should I just do it for that specific table somehow?

    Any help would be much appreciated

    Crabby

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it depends on the version of the mysql server.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Version 5.0

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    default-character-set only changes the default character set used when creating a table. it won't change any of your existing tables.

    first thing you have to do is change all of your tables using ALTER TABLE to have a utf8 collation.

    second thing is to add the collation-server option to your my.ini or my.cnf file and restart your server. alternatively, you can send a SET NAMES command when you connect to the server to switch the connection to the correct collation.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi longneck

    Thankyou for your reply,

    Would I need to alter all tables in all the databases?

    Crabby

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's up to you. at a minimum, the fields you want to hold utf8 data need to be altered. generally i like to make sure that all of my fields have the same collation, unless i have a specific reason not to. it just makes life easier.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks again for your reponse

    Sorry to be a pain, but I have 2 instances of this in my ini file

    PHP Code:
    default-character-set=latin1 
    should I replace latin1 for UTF or do I add it aswell as for both instances?

    Thanks again for your help

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, both.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I still seem to be getting the same problems, I've changed my.ini to say

    PHP Code:
    default-character-set=utf8 
    and I've altered my tables collation to utf8_unicode_ci and utf8_general (is this right)

    This is an example of the problem I'm getting; (375%) is stored as (37·5%)

    I've traced out the rss feed before inserting into the database and all seems to be ok?

    Any ideas what I'm doing wrong, any help would be much appreciated

    Thanks again

    Crabby

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    add some debug code to your application so you can see the output of this command: show variables like '%collation%';
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  11. #11
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    output

    collation_connection latin1_swedish_ci
    collation_database latin1_swedish_ci
    collation_server latin1_swedish_ci

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    did you restart the server after you changed the my.ini file?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  13. #13
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Doh I only retstared IIS!!

    ok it now outputs

    collation_connection utf8_general_ci
    collation_database latin1_swedish_ci
    collation_server utf8_general_ci

    it still has them annoying chars?

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the ones you already put in are going to be broken. you can write a php script that uses iconv to fix that. but new stuff going in and coming out should be ok.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  15. #15
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried emtying the table and running the script again and it's still showing the chars, do I need to re-create the table?

  16. #16
    SitePoint Addict crabby80's Avatar
    Join Date
    May 2007
    Posts
    387
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Longneck do you use iconv to change the collation_database variable for the existing databases?

    Crabby


Bookmarks

Posting Permissions

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