Originally posted by dwno@Mar 25 2005, 10:58 AM
The table 'products_description' mentioned has a 'language_id' column, so for each language installed you will get multiplied results if you export the entire table as described.
I appreciate Eivind's comments about the multi-language tables. In fact, it took several PMs to persuade him to contribute his thoughts, because I didn't have any personal experience with alternate languages. Thanks, Eivind.

But, I've had a chance to look into the issue of doing updates with scripts on multi-language tables and came to the conclusion that… working with all the languages in the same worksheet is a good thing.

If you want to work with only one language at a time… you can do what Eivind suggested; you can also export / import into Excel, sort on language_id, and delete the rows you don't want; or you can export / import into Excel, copy the worksheet to additional worksheet(s), sort on language_id, delete the rows you don't want, and have a worksheet for each language.

I'm going to try to convince you should be work on tables with multiple languages in them as one complete table, with all translations intact.

In ZenCart (v1.2.4), 12 of the 87 tables have a language related field.

They are
1) categories_description, coupons_description, products_description, reviews_description
2) languages, manufacturers_info, record_artists_info, record_company_info, template_select
3) orders_status, products_options, products_options_values

The first group of four tables have alternate language descriptions contained in the same table. These tables are prime candidates for using scripts.

The second group of five tables use only a language flag, the tables are small, you set them and forget them, and it would be a waste of time setting up scripts to update them. Maintain them in Admin.

The third group of three tables is a mixture of the first two. There is multi-language content in the table, but, it is possible that you will set them and forget them, they may not justify the effort to setup a SQL worksheet, and updating in Admin may be the better method… depending on how many records you need to maintain.

You can evaluate the database schema and decide for yourself.
To get a copy of the ZenCart database schema…
In phpMyAdmin, on the Structure tab, below the table names, you should find `Data Dictionary`. Click on it, when the new page comes up, click in right frame, do <ctrl><a>, <ctrl><c>, go to a new worksheet in the ZenUpDates workbook, go to B1, and paste. There will be some objects on the page that you will want to remove. In row 1, <right-click> on the two icons and &#39;cut&#39; them. Delete row 1. Do <end><home> and at the bottom of the table you will see another icon. <right-click>&#39;cut&#39; twice to remove it. You can highlight the four columns, do `format / column / autofit selection` and have a table of all of ZenCart&#39;s tables and fields that you can study and refer to.

Regarding the first group of tables.
When you export the tables with all of their records, your are rewarded with enhanced editing abilities.

By sorting on the table_id field and then the language_id field, you can <arrow-up>/<arrow-down> between description fields and ensure the translation, embedded html code, etc. is correct and is consistent between languages. You can create formulas to check if you have an alternate language description for every table_id and, if not, insert a row, copy the current record into the new row, edit language_id and description.

The reviews_description table is an excellent candidate. Someone writes a review that you think should be seen in all the languages. Insert a row, copy record, change language_id, translate the text, and the review can be read in your other languages.

After you have edited the table(s) for missing records, translations, consistent presentation, adding reviews to other languages, etc., etc., replicate the formula to create the SQL script, and run it. Your site will be consistent from language to language and I think your customers will appreciate the professional image it projects.

Your `tablename_description` worksheets become your original sources and backing up these important tables is no longer needed. Any changes are first made in your worksheet and then the table is updated via a script. Making changes to your descriptions, even adding a whole new language, etc. can all be done offline, at your pace, and uploaded at one time.