How to modify the collation of an existing MYSQL database?

I know it’s scary to do an update install, but I’ve done it several times without losing anything.

On most hosts, you can use cPanel’s backup first to save your files, db, aliases, etc. to separate files on your local machine and later, upload those files if anything goes wrong.

Bob

I haven’t looked into the inner workings of ditto yet, but the problem is related to the fact that you need to tell mysql to use another collation when sorting the results:



More about that here: http://dev.mysql.com/doc/refman/5.0/en/german-character-set.html

I know it’s just a pointer, since I haven’t implemented it myself yet. But maybe it helps you.

Hold everything!!

I looked at the Ditto code and Ditto does a custom sort using strnatcmp() which has known bugs for foreign-language sorting.

There is some code here that might solve the problem for you:

http://www.php.net/strnatcmp

You’d have to add that function to the ditto.class.inc.php file and change Ditto’s call from strnatcmp() to _strnatcmp().


Bob

Thanks for the effort Folks.
Bad news is that I could not get Ditto to work with that hack.

It is getting late around here so I will sleep on it for now.

EDIT: OK I could not sleep, this problem just keeps rolling around in my head. Anyway, I got the ditto debug console to work (the url had a & that was causing it to break, changed it to a simple & and it opened) and noticed that in the ’Prefetch Data’ tab the order is perfect, just the way I want it. However in the ’Retreived Data’ tab the order is all messed up. What could this mean? I wonder if this has been a Ditto bug all along? I wonder why Ditto doesnt just use the query sort order.

Anyway, I will poke around for a little longer. I wonder if I should move this over to the Ditto forum?

Because I would run sooner and later in the same problem, I made a test with umlauts in the titles of articles, and Ditto was sorting them correctly, both on my local test system as on the remote server as well, without any hacks.

I wonder if you’re both using the same version of Ditto?

I think Ditto does it that way so that if there are numbers in the items to be sorted, they’ll be sorted sensibly. Otherwise, img10.png would come before img2.php.

Bob

I’m using Ditto 2.0.2. And if I understand him correctly, his problem is not sorting numbers, or strings with numbers in it, but sorting last names with umlauts in it.

Quote from: nightsignals at Nov 02, 2007, 09:59 PM

I’m using Ditto 2.0.2. And if I understand him correctly, his problem is not sorting numbers, or strings with numbers in it, but sorting last names with umlauts in it.

Right. I was just explaining why Ditto uses the custom sort routine.
Bob

Quote from: BobRay at Nov 02, 2007, 10:22 PM

Right. I was just explaining why Ditto uses the custom sort routine.

Hi Bob - how do I trigger the customsort? Because by default, it doesn’t kick in. That means if I sort by title, I get:

-img1
-img10
-img13
-img2

instead of:

-img1
-img2
-img10
-img13

(as strnatcmp is doing)

By the way, strnatcmp can indeed not handle the umlauts correctly and puts them at the beginning. But as I said, it seems to play no role when soring entries with Ditto.

I didn’t look that closely at the Ditto code. Maybe there’s a parameter setting that influences the sort or maybe you have a different version of Ditto.

Since the problem is clearly Ditto-specific, this should really be moved to the Ditto forum.

Bob