yet another character encoding problem

hi, i’m a newbie and i’m trying to set up a web site for some friends.. i checked out a bunch of cms alternatives, but i decided to go with modx since the manager looks quite simple (even for my non-geek friends who’ll run the site) and it’s also really easy to make templates as it’s pure html and css..
i thought i’d experiment first by installing modx on my own computer (installed LAMP, running Linux/Ubuntu 9.10 )

problem is i ’m trying to make Greek work in modx revolution, but all i get is ?????? ? ??

during installation i set
Connection character set: utf8
Collation: utf8_general_ci

i think i’ve set default encoding in mysql to utf8 as well (followed these instructions https://lists.ubuntu.com/archives/ubuntu-users/2006-February/067367.html)
(although i don’t know exactly how to check if it’s actually set..)

similar thing with modx evolution.. i get most characters correct but the ones with accent like "ά" are again translated to "?"

any ideas please?

ps. even if i fix this on my computer, when it comes to the actual server, is setting the mysql character-set up to me or is it only up to the web hosting provider?

Make sure your database collation is what you specified in the install. Often when creating databases in MySQL, the default is latin1_swedish_ci, and trying to store utf8 data in this charset will cause this.

i tried a bunch of ways to change the collation of the existing database but i just couldn’t do it..

i created a new db and reinstalled modx and it now works fine with greek characters..
thnx a lot for the help..

Quote from: OpenGeek at Apr 01, 2010, 08:34 PM

Make sure your database collation is what you specified in the install. Often when creating databases in MySQL, the default is latin1_swedish_ci, and trying to store utf8 data in this charset will cause this.

The collation is responsible for sorting only, it doesn’t have anything to do with the representation of characters - this is what the charset is for (see http://en.wikipedia.org/wiki/Collation).

Cheers,
Jan

Quote from: enigmatic_user at Apr 02, 2010, 11:52 AM

Quote from: OpenGeek at Apr 01, 2010, 08:34 PM

Make sure your database collation is what you specified in the install. Often when creating databases in MySQL, the default is latin1_swedish_ci, and trying to store utf8 data in this charset will cause this.

The collation is responsible for sorting only, it doesn’t have anything to do with the representation of characters - this is what the charset is for (see http://en.wikipedia.org/wiki/Collation).

Cheers,
Jan

The collation implies the charset Jan.

Hurrah!
Yes, really hurrah!
No more problem in mixing latin and greek characters. Here is the secret...

I spent hours and hours wondering why phpMyAdmin and MODx were unable to save Greek text in the same chunk as English, French and German text.

The database was in UTF-8.
The MODx backend end was set to UTF-8.
The setup had been done in UTF-8.
The database collation was UTF-8.
But the damned Greek characters were never saved correctly.

All the time ? chars instead.

The French "ç" was saved without problem. So, some iso-8859-1 was certainly still here.
Now, it’s time to mention that the database had been converted from iso-8859-1 to UTF-8, using the "Operations" tab of phpMyAdmin.

Surprisingly, I observed that the collation (interclassement) of all tables were still iso-8859-1. So, for the problematic table, I changed the collation from iso-8859-1 to utf-8. But it was still not working.

Then, I found out that the table fields themselves have their own collation. Your database can have an utf-8 collation, your table too, but the field still have an iso-8859-1 (latin-1) collation.

I changed the problematic field to have an utf-8 collation too. And now, this works perfectly. At least with phpMyAdmin and probably the same when editing with MODx.

I’m not an expert in charsets and encodings although I read quite a lot above them.
Currently, I represent myself Collations like "Views".

As conclusion:

- The data are stored with a given number of bytes.
This is determined by the encoding:
- 1 byte/char for iso-8859-1 ... iso-8859-7,
- 2 bytes/char for UTF-16
- variable number of bytes/char for UTF-8.

- The bytes are mapped a certain way to characters and vice versa. This is the role of the charset. For example, the iso-8859- series maps into different "alphabets".

- Now, from what I understand, we tell how we want to access the data. For example, we can ask to "read" or "write" data in UTF-8 storage in an iso-8859-1 "mode". This is the role of the collation.
Collation acts like a filter.

We often take care about "encoding" and "charset", but we often forget the collation, which acts behinds the scenes. And this is where problems arise.

To make things even more complicated, the collation can be set at 3 levels: database, table and table column (i.e. field).

At least, that’s my understanding.
I would be happy if some expert could confirm or invalidate it.

Quote from: Jul at Jul 01, 2010, 06:42 PM

Then, I found out that the table fields themselves have their own collation. Your database can have an utf-8 collation, your table too, but the field still have an iso-8859-1 (latin-1) collation.

This would explain why changing the collation of the db didn’t fix my problem, but deleting it and making a new one with the correct collation set from the beginning solved it. (Obviously all tables and fields defaulted to the databases’ collation).

Congrats on figuring this out! (Sounds like you spent a lot of time reading stuff till you find it)

(...)but deleting it and making a new one with the correct collation set from the beginning solved it.

It is not neccessary to delete the table.

Just go in phpMyAdmin. First make sure that the collation to access the database is the correct one, or change it from the "Operations" tab.

Then, click the table name. Go to the "Operations" tab again and change the table collation.

Lastly, for your table, click the "Structure" tab. Click the "Pencil" icon to edit the properties of the table column. Use the drop list to change the collation for that column.

When you change a database collation, I assume this is only for new tables. My assumption is that phpMyAdmin does not change the collation of existing tables for compatiblity reasons with interfaces that already query the database.

Congrats on figuring this out! (Sounds like you spent a lot of time reading stuff till you find it)

In fact, I found it by exploring phpMyAdmin, not by reading. However, it’s true that past readings helped me to understand things afterwards.

(...) click the "Structure" tab. Click the "Pencil" icon to edit the properties of the table column. Use the drop list to change the collation for that column.

This is long to do. I tried checking several checkboxes to edit simultaneously several columns, but it does not work. I’m writing some software to automate this.

You probably have the software you need, e.g. Notepad++ or any advanced text editor. Export the tables as SQL, load as text, search and replace the relevant setting, save and import again.
Crude but effective - or simple and elegant?
KP