Zen Cart product catalogue includes product categories and product information including descriptions for the products. osCommerce also follow the same database structure, but product tables in osCommerce have fewer fields than in Zen Cart. This structural difference should be taken into consideration while migrating from osCommerce.

While migrating the product catalogue from osCommerce to Zen Cart, you should also be aware that besides migrating the database we also need to migrate the product images to appropriate folders from osCommerce to Zen Cart.

osCommerce does not support product types. So the default value for the products type field will be used for products imported to Zen Cart. This means that all products will belong to the Product—General type.

If you are migrating other data, such as customers, orders, and so on, besides product catalogues, you have to consider interrelations among these tables. Remember that products are related to orders and orders are also related to customers. Thus, customers are related to some products. These interrelations must be understood beforehand and planned accordingly to decide which tables need to be migrated.

You have to remember that Zen Cart and osCommerce databases are not identical. Some of the tables in Zen Cart database have more fields. However, the fields available in osCommerce tables are mostly available in Zen Cart tables with similar names. For some fields, the data type or length is different. However, this will not create a problem while migrating data from osCommerce to Zen Cart, as new field types and their length in Zen Cart tables can accommodate the original data types and the length of the osCommerce table fields.

Considering the differences in both the databases, you have to understand that all fields in Zen Cart tables will not have values except the default values for those fields. For example, osCommerce categories have no descriptions, but Zen Cart categories have. While exporting osCommerce categories data to Zen Cart, the description field will remain blank. You have to add the descriptions to these fields manually.

The general look and feel will not be exported to the Zen Cart shop. Usually, Zen Cart shops have their own look and feel, and you can adapt their look and feel to the old osCommerce shop by modifying the Zen Cart template. Customizing the Zen Cart template has been discussed in detail in Chapter 4 , Customizing Look and Feel.

Some other elements of look and feel are the display of product categories, display of product images, and so on. osCommerce cannot have images for categories. Therefore, in Zen Cart, you need to assign icons for categories manually after migration. You also need to transfer the product images to the images directory of Zen Cart installation.

While migrating your osCommerce products to Zen Cart, you have to ensure that service on both shops remains uninterrupted. If you are migrating from a live shop, ensure that the interruption to the shop is minimal. And also for migrating to a live shop, you have to pay special attention to ensure minimum interruption to services.

It is best to do all preparatory work beforehand and then make the shop offline for some time to complete the migration. Before the actual migration, do some testing on your local server.

As taking the shop offline will not be possible all of the time, you can run the shop in its original location and make a copy in another folder. Then, try to migrate to the new instance of Zen Cart. This will allow you more time to migrate data from osCommerce. Once the instance works fine after migration, make this instance the main shop.

First, compare the categories tables for osCommerce and Zen Cart. The tables are almost similar, except for one field. Zen Cart's categories table has an extra field named categories_status. So, while migrating data from the osc_categories table, you will not get data for this categories_status field. However, this will not create a problem as it takes a default value of 1 (meaning active).

The categories_descriptions table contains descriptions of each category. Both zc_categories_descriptions and osc_categories_descriptions tables are identical. So, migrating data from osc_categories_descriptions table to zc_categories_descriptions will be straightforward.

There are many differences between the zc_products and the osc_products tables. There are at least 22 extra fields in the zc_products table. Fields available in the osc_products table are also available in the zc_products table, although, there are differences amongst common fields. For example, the products_model field in zc_products table is varchar(32) whereas same field in osc_products table is varchar(12). This means that exporting data from osc_products to zc_products is not a problem for this field, but exporting from zc_products to osc_products may create a problem as the zc_products.products_model field is larger than the osc_products.products_model field. On the otherhand, zc_products.product_weight is of float type which is decimal in osc_products. As a float type can accommodate a decimal type, it will not be a problem. Analyzing the structure of both the tables, you can see that exporting data from osc_products to zc_products will be not a problem, but the reverse may be.

Similarly, zc_products_attributes table has 27 fields, whereas the osc_products_attributes table has only 6 fields. These 6 fields are also available in the zc_products_attributes table in an identical form. So, there will be no problem exporting data from osc_products_attributes to the zc_products_attributes table.

Product options are stored in the product_options table. Zc_product_options table has 11 fields while the osc_product_options table has only 3 fields, which are identical to the 3 fields available in the zc_product_options table.

Regarding product options, Zen Cart has an extra table—products_options_types. This table contains three fields: products_options_values_id, language_id, and products_options_values_name. osCommerce has no such table and you will not get these fields.

The Zc_products_options_values table has one extra field - products_options_values_sort_order. The other two fields are identical to osc_products_options_values.

The following tables in both Zen Cart and osCommerce are identical:

Product types are not supported in osCommerce. So, you will not find the product_music_extra, product_types, product_types_to_category, and product_type_layout tables in osCommerce.

Back up both the osCommerce and Zen Cart databases. It is always recommended that whenever you make any change manually to the database, you back it up first. Once the data is lost you cannot get it back unless you have a recent backup. For both the databases, backup at least the categories and the products tables. You can do it easily from phpMyAdmin tool by exporting the whole database.

Login to cPanel (if applicable), and open phpMyAdmin. Select the existing osCommerce database that you want to extract information from. Now, click on the Export tab.

Under the Export section, highlight all tables that start with categories_ and products_. You have to select at least the following tables:

Under the SQL options on the right hand side of the page, check Data and Complete Insert options.

Now, at the bottom of the page, check Save as File. Click on Go, save the file on your hard disk and name it something like osc_data.sql.

You have already extracted information from the osCommerce database, which needs to be imported to the Zen Cart database. Now, importing to Zen Cart can be simple or complex depending upon your situation:

We will discuss importing data for both cases, but start with the simple migration process.

First, let us suppose that we are migrating our osCommerce categories and products to a new Zen Cart shop which has been installed and configured, but no categories or products have yet been added to it. In that case, the categories_* and products_* tables are empty. It may also happen that you have installed sample data, and these tables are populated with the data. In that case, just delete the records from these tables and make them empty.

Once you find the tables empty, you are ready to import the osCommerce data. Start phpMyAdmin, and go to the Import tab. In the File to Import section, click on the browse button and select the osc_data.sql file on your hard disk (which you have saved in Step 2). Then, click on the Go button. Your data will be imported if everything is alright. Otherwise, some error messages will be thrown up, and from those messages, you will get clues to look into the problems.

On the successful completion of the import of the osCommerce product data into the Zen Cart database, you need to import product images from osCommerce. Generally, product images in osCommerce are stored in the /images directory under osCommerce installation root. Copy the images and sub-directories (e.g. dvd, matrox, sierra, and so on) to the /images directory under the Zen Cart installation root. Both the images for categories and for products need to be imported.

You can download two contributions from the Zen Cart website. The first one is osCommerce Data importer contribution by Albert Savage, available for download from http://www.zen-cart.com/index.php?main_page=product_contrib_info& cPath=40_41& products_id=918. This package includes three separate scripts which import customers, products, product description, categories, category descriptions, category structure, specials and reviews. You have to extract the zip file and upload these three scripts to the Zen Cart installation directory. For all the three scripts, you have to configure the following variables:

//Source Database with OSC records
$source_db_host = 'localhost';
$source_db = 'database';
$source_db_username = 'user';
$source_db_password = 'password';
$source_db_table_prefix = '';

The above variables are for the source database, that is, the osCommerce database from which data will be imported. Then, you have to configure variables for the destination database, that is the Zen Cart database to which you want to import the data.

//Target Database with ZEN records
$target_db_host = 'localhost';
$target_db = 'database';
$target_db_username = 'user';
$target_db_password = 'password';
$target_db_table_prefix = '';

After changing the configurations and saving the file, point your browser to one of the above files, for example, http://yourdomain.com/import_osc_customers.php to run the script.

The script will display the progress of exporting and importing data, and finally notify you of its success. You have to run the other two scripts: import_osc_products.php, and import_osc_orders.php as well.

The second contribution that can be used for importing data from osCommerce to Zen Cart is osCommerce to Zen Cart Conversion Script by Michael Morris, available from http://www.zen-cart.com/index.php?main_page=product_contrib_info& cPath=40_41&products_id=946. This package contains a single script named oscommerceimport.php. Once downloaded, extract the zip package and upload the oscommerceimport.php file in your admin directory. Now, point your browser to the file, that is, http://yourdomain.com/admin/oscommerceimport.php. The page will ask you for the osCommerce database name and the table prefix. Enter this information and click on the Go button. The data from osCommerce will be imported to the Zen Cart database. Like the other script, it will destroy all the product, category, and order data in Zen Cart. As you need to use this script only once, delete the file once the data import is complete.

We have already mentioned that when your Zen Cart shop has existing categories and products, importing categories and products from osCommerce database becomes more complex. This is because the Zen Cart categories and products use the same category and product IDs, which results in conflicts of uniqueness of these primary keys. Before importing osCommerce data, we have to modify them in osCommerce to avoid conflict with the Zen Cart table IDs.

First, check the highest ID assigned to categories and products. For example, your existing Zen Cart shop has 66 categories, and the highest value of the categories_id field is 66. You can determine this by running the following query in the Zen Cart database:

SELECT MAX( categories_id )
FROM categories;

This will return the highest value for the categories_id field. Similarly you can get highest value for products_id field:

SELECT MAX(products_id )
FROM products;

It may return something like 181. Now, we have got the values which we cannot have in osCommerce data. In our osCommerce data file, we must not have any category whose categories_id is less than 67, and any product whose products_id is less than 182.

Changing the values of the categories_id and products_id fields is tricky, as these are also used to link to the other tables. The following tables we have selected from the osCommerce database use the categories_id field (as the primary or foreign key):

So, if we change the value of the categories_id for one category, we have to change that value in all these three tables simultaneously. We can use the UPDATE statement for this. As a rule, we may increase the value of all category ids by 100. So we will add 100 to the value of the categories_id field. We can do this by running the following query in the osCommerce database:

UPDATE categories SET categories_id = categories_id + 100;
UPDATE categories_description SET categories_id = categories_id + 100;
UPDATE products_to_categories SET categories_id = categories_id + 100;

These three UPDATE statements will add 100 to the value of the categories_id field.

Now, we will update the values of the products_id field. The following tables use the products_id as the primary/foreign key:

Similar to the categories_id, we will change the products_id in these tables by executing the following queries:

UPDATE orders_products SET products_id = products_id + 200;
UPDATE products SET products_id = products_id + 200;
UPDATE products_attributes SET products_id = products_id + 200;
UPDATE products_description SET products_id = products_id + 200;
UPDATE products_notifications SET products_id = products_id + 200;
UPDATE products_to_categories SET products_id = products_id + 200;

These queries will update the products_id values in related tables.

There is another thing that we have to update in the categories table. The parent_id field in the categories table indicates the parent category ids. If the value of parent_id is 0, it is the root category, and if it has a value, it is under some category. As we are changing the category's id, we must also change the value of the parent_id to reflect the exact category id. But for root categories, we will not change the parent_id. So, we have to run the following UPDATE statement:

UPDATE categories SET parent_id = parent_id + 100 WHERE parent_id > 0;

We also need to update the products_options_* tables. As the product_options_id field may have duplicate values with Zen Cart the products_options_* tables, we need to increment the values in osCommerce first. Similar to categories and products, determine the highest value of products_options_id in Zen Cart, say 90. Then increment the value of products_options_id in osCommerce tables as follows:

UPDATE products_options SET products_options_id = products_options_id + 100;
UPDATE products_options_values_to_products_options SET products_options_id = products_options_id + 100;

And for updating products_options_values_id, run the following queries (assuming that existing highest value of this field is 95):

UPDATE products_options_values SET products_options_values_id = products_options_values_id + 100;
UPDATE products_options_values_to_products_options SET products_options_values_id = products_options_values_id + 100;

After these updates, you are ready to export data from the osCommerce database. Follow the procedures described in Step 2 to export data from osCommerce. Save the exported data file as osc_data2.sql.

Now, it is time to import osc_data2.sql into the Zen Cart database. Start phpMyAdmin and go to the Import section. In the File to Import section, click on Browse and select the osc_data2.sql file. Then click on the Go button. All data will be imported to the Zen Cart database.