SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Going Postal Over Collation

    I thought my MySQL upgrade was really cool until I started having problems with the new "collation" stuff. Now it's driving me nuts!

    Can someone explain to me in plain English what collation is all about and how to either get rid of it or deal with it? I used to import data into my tables via csv files and Navicat. I can now import csv files with my phpMyAdmin upgrade.

    However, I have to go back and forth between the two because of collation, and sometimes neither one works. I wind up with columns that display 0 instead of (null) and appear to push other columns over into the wrong column.

    And that's just my LOCAL problems. Publishing tables online is another problem, because my host hasn't upgraded to the latest MySQL yet.

    One scheme I've thought of is to fill every NULL cell with something distinct, like XXX, then use PHP to somehow render XXX invisible, or null. Do you think that would work? And is anyone aware of a good collation tutorial? MySQL's website remains Greek to me. Thanks.

  2. #2
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I probably should have just added this to another thread I started at http://www.sitepoint.com/forums/show...=1#post1693067 I completely spaced that one out. But any additional information would be appreciated. I'd just like to find a really simple method for dealing with this stuff.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by geosite
    One scheme I've thought of is to fill every NULL cell with something distinct, like XXX, then use PHP to somehow render XXX invisible, or null. Do you think that would work?
    yes, that's a good strategy, and quite workable

    as long as you can actually use 'XXX' (i.e. if it's a character field)

    for numeric fields, i prefer to use the value 937937

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, that's a good strategy, and quite workable

    as long as you can actually use 'XXX' (i.e. if it's a character field)

    for numeric fields, i prefer to use the value 937937

    Wow, thanks for the quick response. Is 937 a standard code of some kind? It looks familiar. I'm going to try inserting 937937 in all my int fields first and see what happens. I suspect I won't have to mess wiith my VARCHAR fields at all.

    Thanks.

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question...If I fill a column with 937937, should I still designate that field as NULL if I'm going to try to find a PHP script that translates 937937 as NULL?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, 937 is a magical number!!



    and of course, the field has to be declared NULL if you hope to update it to NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •