Going Postal Over Collation

[geosite]

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.

[geosite]

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.

[r937]

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

[geosite]

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.

[geosite]

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?

[r937]

yes, 937 is a magical number!!



and of course, the field has to be declared NULL if you hope to update it to NULL