SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing Both English and Spanish information in the same table

    Have a customer site that runs in both English and Spanish (one language at a time of course) and I thought I had everything planned out, right up until I added the Spanish information into the products table. Once entered all the special Spanish characters became 'hieroglyphics' for lack of a better term.

    For example: MARRÓN/NEGRO added to the table has become MARRÓN/NEGRO

    The db is collated latin1_general_ci is there another collation I should be using that would work for both languages?

    Sorry if this is an elemental problem, first time I have ever used a foreign language in a database.

  2. #2
    SitePoint Enthusiast Fallen Angel's Avatar
    Join Date
    Sep 2006
    Location
    UK
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are a couple of collations for Spanish - one for modern Spanish, and one for traditional Spanish, xxx_spanish_ci and xxx_spanish2_ci (latin1_spanish_ci) (info from here: http://dev.mysql.com/doc/refman/5.0/...code-sets.html).

    I've just come across this post as well that might be useful to you.
    "You don't need eyes to see - you need vision"
    ecanus.net: Poetry, smileys and stuff

  3. #3
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fallen Angel View Post
    There are a couple of collations for Spanish - one for modern Spanish, and one for traditional Spanish, xxx_spanish_ci and xxx_spanish2_ci (latin1_spanish_ci) (info from here: http://dev.mysql.com/doc/refman/5.0/...code-sets.html).

    I've just come across this post as well that might be useful to you.

    hmm, the problem here is that both the English and the Spanish are stored in the same table ...... UGH

  4. #4
    SitePoint Enthusiast Fallen Angel's Avatar
    Join Date
    Sep 2006
    Location
    UK
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dc dalton View Post
    hmm, the problem here is that both the English and the Spanish are stored in the same table ...... UGH
    Ah, I see what you mean...UGH indeed!

    Hmmm....I can't say it's something I've tried to do before either. The only thing I can find that may be of any use then if the table needs to be "multilingual" is this article.
    "You don't need eyes to see - you need vision"
    ecanus.net: Poetry, smileys and stuff

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about utf8_general_ci ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dc dalton View Post
    hmm, the problem here is that both the English and the Spanish are stored in the same table ...... UGH
    storing them in separate tables would be even UGHER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    storing them in separate tables would be even UGHER
    Well after trying a ton of different things this is exactly what I ended up having to do. I created an _eng table collated in latin_general and then an _esp table collated in latin1_spanish_ci .... seems to have worked fine.

    Makes the code a little uglier (having to test for what language they are using) but I was storing the language variable in their session anyways.

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Could you not set the collation as utf8_unicode_ci, store both the english and spanish versions in the same table and have a 3 character language field to record a three character code to denote which language the record is then when you search for the product information you could select the language required.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    9.1.1. Character Sets and Collations in General

    We're talking about character sets here, not collations, as far as the "hieroglyphs" are concerned. Collation is for sorting, character set is for interpreting the bits into characters.

    If you were to only change the collation, you'd still bungle up all your text since you're not using a character set which can represent those glyphs.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What chactacter set is the site in question using?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •