I used Mysql4 for quite a while now and since Mysql5 is availlable at my provider, I want to upgrade to that. I know my way around MySQL a bit so I had no problem changing the settings in XOOPS to point to the right database and to insert everything into that new database via PhpMyAdmin.

Now my problem is this: everything in my database is in UTF-8 (I made sure of that since the first day I used the database in mysql4). I checked everything and I am viewing phpmyadmin in UTF-8, phpmyadmin is in UTF-8 and I see the data correctly (I use japanese and french on my site and all characters are fine). I made sure that XOOPS also is encoded in UTF-8. The browser is in that mode and the languages setting point to UTF-8 (and where set a long time ago when I first fired up xoops).

The data displayed on my browser displays bad characters. Wich can only mean that there is something wrong between XOOPS and the database (they are not in sink). I made sure of this by forcing the encoding of the transfered data with a "SET NAMES UTF8" function and then the data displays fine BUT once I log-off, I cannot login on my site anymore and can't navigate around.

If I don't do the "SET NAMES" function, All the characters I enter in the browser display fine but when I look in the database, they become corrupted. If I force the "SET NAMES" then enter the same characters again, they display fine in the browser and in the database.

My problem is I don't know the protocols to call data to and from the database in PHP so theres something I did wrong with that "SET NAMES" function.

Can somebody help me?

After A LOT of searching around, I finally found the answer and I'll post it so that anybody encountering the same situation doesn't have to go through the same ordeal as me.

It seams that trough a problem of some sort (probably centered in the php version used), the encoding and setnames function where not in sinc. The following should be a standard hack on future XOOPS versions if they want to claim that it is pure UTF-8 for it will force most systems to the UTF-8 encoding (my provider didn't let me access the php settings). The first thing, of course is to make sure the global.php file in your languages folder points to UTF-8 and not another encoding. It should be done for all your languages and you'll find that encoding near the end of the file.

Then, go to your Xoops/class/database/ folder and edit the file: mysqldatabase.php

In the "connect" function, right before the "return true" part, insert those 3 lines.

Your all set to go.
Try all the languages in the world that you want and then go see in your database with mysqladmin and you'll see that no weird characters are there.

On a fresh installation, people may think that their XOOPS is trully UTF-8 since all the characters display fine but if you take a look at the database itself, you may notice that the encoding is wrongly passed. I noticed it because I had text from a previous installation that was right in the database, hence displayed wrongly in the browser. This will cause big headackes when you upgrade your database or just want to edit stuff directly into it. Friendly warning.

Thank you for sharing this.

I've just solved the problem in a different way. Instead of touching XOOPS codes, I changed the MySQL settings.

I did as following:



Change the config file of MySQL (my.cnf) so that it looks like this:


The most important thing here is the above 2 lines: one to force default character set in MySQL to "utf8", another is to tell MySQL that "do not automatically convert character sets between server and client"

Restart the MySQL server. Bingo!! The characters are displayed properly both in phpmyadmin and in xoops.

Hope this help.