Special characters messed up in ZC Admin

[seans]

Hi there

I am having trouble with the trademark special character.

It shows up perfectly on my local machine in admin and catalog views. But when I try and import/export to my live server I don't get to see the special characters. After the import to the live database, if I check the name and descriptions, I can see the correct trademark special character.

Can anybody help? I've read through this thread but can't make out what exactly it is I need to do.

I am using version 1.3.7 of ZC.

Thanks

[Vger]

This is most likely a problem with the MySQL Character Collation being utf8 on your online server, and the sql file you uploaded being en-iso-8859-1 collation.

This is a very tricky subject and not easily answered in a forum post. Your best bet is to Google for "database problems with utf8 and en-iso-8859-1" or similar.

Vger

[seans]

This is most likely a problem with the MySQL Character Collation being utf8 on your online server, and the sql file you uploaded being en-iso-8859-1 collation.

This is a very tricky subject and not easily answered in a forum post. Your best bet is to Google for "database problems with utf8 and en-iso-8859-1" or similar.

Vger

First of all, what should the collation be? And is it possible for me to change the collation of the online database via phpMyAdmin?

[icouto]

I will try to explain the problems reported in this post as well as I possibly can. Not being a PHP expert, or MySQL expert, or Zen Cart expert, it is quite possible that I will get something in this explanation wrong. If I do, I ask the true experts to feel free to correct me (I will not be offended).

I must admit, that despite my love of Zen Cart, I find it incredibly disappointing that support for unicode and international characters still has not been properly implemented by the wonderful Zen team.

I have come across this same problem before, with other php scripts, trying to implement appropriate support for UTF-8 (many of my sites are in Esperanto, the international language, and I need to be able to type accented characters in several different languages). I need to be able to display these character appropriately both in the functional side of the site, as well as on the admin side. Being an administrator, it is also important for me to be able to use the phpMyAdmin interface to transfer data into and out of my databases from time to time. So, my characters need to show well in ALL interfaces.

I guess the first thing people have to understand, is that there are 3 separate components which are involved:

1) INTERFACE: the html interface which presents text to, and gathers text from the user, in their browser

2) DATABASE: the MySQL database that stores the text

3) CONTROLLER: the php script which moves the text between the database and the interface.

-------------------------
SOLUTION
-------------------------
What we all want, is to change the 3 parts of the system so that the text encoding used throughout is 'utf-8' (which includes just about every alphabet and character set in the world). Changing your entire system to using unicode (utf-8) is better than choosing a specific character set (such as 'english', 'chinese', 'japanese', 'cyrillic' or 'icelandic'), because utf-8 includes *all* these characters and more. This means that your shop would be able to natively support all these languages - should you choose to add any of them in the future!

1) INTERFACE:
When you install Zen Cart, by default all its pages, both in the catalog and in the admin side, are encoded as "English", "iso-8859-1". In order to change the interface to 'utf-8', you must change the /includes/languages/english.php file. Specifically, you have to change the line that says:

define('CHARSET', 'iso-8859-1');

to

define('CHARSET', 'utf-8');


If your shop is already using other languages, you have to make this same change for all installed languages, not just for the default 'english.php'.

This changes the default interface language of the catalogue. But to change the interface of the admin, you have to make exactly the same change in the equivalent admin file:

/admin/includes/languages/english.php (and also in any other language files you have installed there, too).

2) DATABASE:
This is a *really* troublesome step - it is long, time-consuming, tiresome, and requires a lot of patience.

When Zen Cart installed itself, it created default tables in the MySQL database you had specified. These tables, however, by default are not expecting to be holding utf-8 text. So, if you send utf-8 text into these tables, the tables will not store the text 'properly' (ie., it will not display appropriately in phpMyAdmin), because the database itself thinks the text is something else.

In MySQL, you can separately specify the encoding for the *entire database*, for *each table*, as well as for *each text field* inside a table. The encoding, in MySQL jargon, is called the 'collation'.

So, in order to modify your entire table, so that it is all utf-8, you have to use phpMyAdmin to:

a) open each table in the database, and change the collation of each text field in each table to 'utf8_generic_ci'
b) change the collation of each *table* to "utf8_generic_ci'

>>Request to the Zen Car team: it would be nice for someone to create an sql script to do this automatically for us. In future versions of the installer, it would be nice to automatically do every CREATE TABLE with "DEFAULT CHARACTER SET utf8" at the end, if the user has MySQL >= 4.1.

3) CONTROLLER:
So, your interface is now fully utf-8, and your database is fully utf-8. So everything must be right... Unfortunately, there is a third component in our system, which is the php script which transfer text (via sql queries) from/to the user's browser and the database.

The php script does not know that the queries you are making use utf-8 text. So, when you type something international into your utf-8 interface, the script grabs it, and then sends it *with the wrong encoding* to the database. The database accepts what the script is sending, and re-encodes it in utf-8. So, the result is, that when you look at your data in phpMyAdmin, it all looks garbled.

When you do a search in your catalogue, the script retrieves information from the database, and sends it to the browser. The browser, however, displays the text it is receiving as 'utf-8' (which it actually is), so it actually displays correctly (even though your database data is a mess).

The only way to fix this is to explicitly TELL the script to use 'utf-8' when talking to the database. To do this, open the following file:

/includes/classes/db/mysql/query_factory.php

Around line 37 you will see:

if (@mysql_select_db($zf_database, $this->link)) {
$this->db_connected = true;
return true;

Add the following text in between the first and second lines, so that you end up with:

if (@mysql_select_db($zf_database, $this->link)) {
if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
mysql_query('SET NAMES "utf8"', $this->link);}
$this->db_connected = true;
return true;

>>Request to Zen Cart team: further down in the same document, there is another function ('selectdb()'), which uses 'mysql_select_db'. I searched through every code file in my shop, however, and could not find any calls to this function at all. Is this old code that should be removed? If not, then the 'SET NAMES' query shown above may need to be added here, too!

---------------------------------------------
DISCLAIMER
---------------------------------------------
You must back up your entire setup before attempting to change any of the files in your site.

I've tested the following patches in my own shop, and it works perfectly. This does not, however, means that it would work fine for yours.

Once again I emphasize, that I am not an expert, and this is a reasonably simple solution I've found, which works with every single language I've tested. It does not mean, however, that it is the BEST or most efficient solution. If you are an expert, and you can improve on these instructions, please do so!

---------------------------------------------
REQUEST TO ZEN CART TEAM
---------------------------------------------
International character support is a very critical issue for many of us using Zen Cart. I suspect that there will be a lot of people looking for the solution I described here, and so I ask you to please post it elsewhere, or include it in an FAQ or manual, as you see fit.

Last of all, I ask you to include the changes described here in the next main release of Zen Cart. As you can see, providing a fully compatible level of utf-8 support is not difficult, and I'm sure you can improve the code, and make it even more backwards compatible (if need be!).

Once again, thank you so much for such a fabulous product. I hope this little contribution will help Zen Cart continue to progress and thrive.

[Vger]

All that you need to do to make the mysql work as utf-8 is to open it in a programme set to utf-8 by default (e.g. PS Pad), and once it is opened click 'Save'.

In addition to setting the default encoding to utf-8 in the language files you also need to do the same as above for all files, because, normally, whenever you open them in a text editor of some sort they are opened as 'Windows' type files in your browser - assuming you use a Windows operating system.
Then phpMyAdmin has to be set to utf-8, ditto php.ini, ditto MySQL on the server.

Actually en-iso-8859-1 covers most accented characters, because it is a European-wide language. Admittedly it does not cover Cyrilic, Japanese or Chinese characters etc. but then neither does utf-8.

Vger

[icouto]

@Vger: your information may not be correct.

Japanese, Chinese, Greek, Cyrillic and most other writing systems of the world are covered by UTF-8.

The default ISO encoding you mention does *not* include most European characters. It does not include even most accented latin characters - European characters such as the Esperanto characters, are excluded.

Last of all, the solution you suggest does *not* enable full-use of international characters. Sure, if all someone wants is for their shop to 'look ok', then depending on your backend server configuration, the simple solution found in the FAQ (just changing the charset) will be sufficient. However, there are many of us who need the product to *really* support international characters. In my case, for instance, I need to be able to see/export/import my data using phpMyAdmin. With the previously provided solution, the information in phpMyAdmin was unusable, even if correct.

By providing full support to UTF-8, the Zen Cart team will not be excluding English or English speakers, but will indeed be making the software even more usable to a wider audience around the world.

[Vger]

The reason that "Esperanto" characters are not covered by European character encoding is because it is not a real language. It was a good idea which never took off.

I'm not knocking utf8 - it's also a very good idea. The problem is not with its implementation for new websites. The problem is with the trillions of websites on servers that formerly used en-iso-8859-1 (Latin1) which have upgraded MySQL and PHP to a level where utf8 is now the only option.

I've spent dozens of hours on this and there is no easy fix for it. Presently I am working on a site coming from a server using en-iso-8859-1 to a server using utf8 - with one site language being in Arabic. It works fine on en-iso-8859-1 and is totally screwed on utf8.

I just wish that the nerds who thought this great idea up had a small thought along the way for the implications of their wonderful idea.

Vger

[icouto]

Vger, the explanation I've given above might account for the problems you have in your new server with Arabic pages. If you read the instructions, you will see that changing everything over to utf-8 is actually not a big hassle - the code changes are actually *minimal*.

I recently had to convert all my sites from an old server (without utf-8), to a new server (utf-8). I had to apply the patch I described above (setting all script queries to use "SET NAMES 'utf-8"), and then usually, all I had to do was download an sql dump of the database in question, open it in a text processor, convert the text to utf-8, resave it, and reload it. I was able to change ALL of my sites over in just a couple of days, and never lost any data. Mind you, I'm not a professional programmer, or expert, and that is all it took me to convert ALL of my scripts.

Apart from Zen Cart, I use other open-source scripts (like the excellent "Vanilla" forum, or ExtCalendar, for a community calendar application). All the scripts suffered from the same lack of *real* support to utf-8. This lack of support comes not from any real difficulty in implementing the solution (as you can see, the solution is simple enough, that even I can implement it), but rather from lack of understanding on the programmer's behalf of internationalisation issues, and utf-8.

Using utf-8 is of GREAT benefit, as once it is implemented, you basically do not have to worry any longer about what language your users are using. In my forums, for instance, people can type in japanese, chinese, arabic, cyrillic, or any other language they choose, and the characters display perfectly well not only on the interface, but also in the database (via phpMyAdmin). As an administrator, this gives me fabulous freedom, to oversee and manage my data directly on the database, as I see fit.

As you said yourself, utf-8 is the way everything is going - for reasons that are obvious for those of us who use it. I thought that the Zen Cart team hadn't implemented it in full yet, because it might be too hard to do - but now I see that this may not be the case.

Last of all: Esperanto is indeed a living language, spoken in over 100 countries, with active associations all over the world, and an active community of speakers. I should know, because I've help put together a few web sites for the Australian Esperanto Association:

http://www.esperanto.org.au

http://forumoj.esperanto.org.au

http://aesk.esperanto.org.au

http://kalendaro.esperanto.org.au

http://libroservo.esperanto.org.au - using Zen Cart, but currently under renovations! Come back in a couple of weeks to see the new improved site!

You can find out more general information about Esperanto at the multi-lingual Esperanto information centre:

http://www.esperanto.net

Or at the site of the Universal Esperanto Association:

http://www.uea.org

Or try doing a quick (and free) fun online course at:

http://www.lernu.net

And good luck at implementing UTF-8 in your current sites. Even if it takes you a while to work it all out, from my own experience I can tell you that it will be worth it in the end, as once it's done, it will be the end of all your charset worries.

[Vger]

I knew when I said Esperanto wasn't a real language that you wouldn't be best pleased. But I'll stick by that all the same. It remains a 'hobby' language for a few dedicated followers, as far as myself and most of the rest of the world is concerned.

The whole idea of it was to unify the world by getting everyone to use Esperanto as a common second language. That did not happen, and never will.

Furthermore, there's no need to have Esperanto as a common second language for the world - because the world now has one, it's called English. Children in China, Japan and Russia learn English as their second language. Children in India and Pakistan who get an education learn English as a second language, because of their cultural heritage. Put those together with all those countries which use English as their first language and you've got most of the world's population covered. As for the French - well, they pass laws to try and stop the spread of the use of English. C est la vie!

Back on topic.

There's no doubt that utf8 is good for new websites and new servers. But it does create problems when transferring websites from older servers which use en-iso-8859-1 to new servers which use utf8. Web hosts like ourselves just have to deal with it on a day to day basis. I do actually look forward to the day when all sites are utf8 - it will make our life a whole lot simpler. It's the transition that's a real pain in the ****.

Vger

[icouto]

Vger, glad to see that you've come around to utf-8. Good for you.

As for your off-topic views on Esperanto, I just hope that eventually you will drop your English-centred view of the world. As an English speaker, it may seem to you as if the world already speaks English, but that is certainly not the case. Consider, for instance, that I've seen Brazilians who visited Australia, and commented to me that they should have never bothered to try and learn English, because "everyone in Australia speaks Portuguese". These same Brazilians tell me that Portuguese is now being taught in places as far as Universities in Korea and China (which apparently is true). Similarly, there are many Chinese living in Australia for years, who have never learnt any English, because everyone they relate to - even in shops - speaks Mandarin, Cantonese or even Hokkien. Indeed, I've heard more than one person say to me, that the most useful and international language they'd ever learnt was Mandarin, because it did not matter where they went in the world, there was *always* someone there who could speak it!

There are studies that confirm that now there are more speakers of Esperanto than many other national languages (like Icelandic, unfortunately). Esperanto is a great choice, not just because it has a true neutral, international culture, but mostly because it is easier to learn than any other language - you can be absolutely fluent in a year.

Esperanto also and has a helpful community of speakers all around the globe - which you don't get by learning any other language. Having myself through Esperanto been able to relate with Germans, Americans, Japanese, Koreans, British, Brazilians, Indonesians, Chinese, Zimbabwans, Australians, Slovakians, Dutch, Russians, Italians, and many other nationalities, I must tell you that the experience of developing a relationship with them in Esperanto is a lot different to doing it in English (or struggling to try and use their national language). Esperanto allows us to relate in an equal level, and to help each other equally cross the cultural divide - rather than expecting that the other will do all the effort.

The Esperanto community is wide, and vibrant, and has a fascinating history and culture. By making derogatory comments about the language, you may unwillingly be offending the many Esperanto musicians, authors, scientists and journalists who help each other around the world.

Take some time, and have a look at some of the links about Esperanto in my previous post. They are a good starting point for you, and may open doors for you which you didn't even know were there to be opened!