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:
-
INTERFACE: the html interface which presents text to, and gathers text from the user, in their browser
-
DATABASE: the MySQL database that stores the text
-
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!
- 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).
- 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.
- 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.
