Opencart 1.5.x Multistore – Associating Existing Data

The Opencart Multistore feature is a great addition for retailers requiring multiple stores, managed via a single administration area. Setting up multistore in Opencart is quite easy and can be accomplished in a few minutes.

However, after visiting the new store you’ll immediately see that existing products, categories, customers, page layouts etc. have not transferred over. Ouch! The new store is completely empty.

Opencart works like this on purpose, assuming one simple business rule. Each “store instance” contains unique data, so categories for instance, belong to a single store by default and Opencart assumes each store will have different categories.

As the original store contains a certain product, does not mean that the second store will also contain this item. The assumption is perfectly valid and I imagine will cover the majority of situations. If certain items are needed for the new store, each item will have a “stores” field on the “data” tab for each item. Opencart makes the assumption that you’ll go through and manually select such items – for larger catalogues this is not time viable.

Associating Existing Data with a new Multistore Instance

There are times when all data (products, categories, information pages, layouts, manufacturers etc.) need to be transferred to the new store, in one fell swoop! At present, Opencart doesn’t have functionality to do achieve this out of the box. There are a some very good extensions on the Opencart marketplace, but the best extensions paid and rather expensive in my opinion. Each to their own though.

To achieve our goal you’ll need to run a few very basic queries. As a heads up for large catalogues, the query is rather slow and will associate all Opencart data will the new store.

Assume the new store id (after adding the store, go to System > Settings and hover over the “[edit]” text for each store – you’ll see a “store_id” variable in the query string, or find via looking directly at the “oc_store” table) is 7 and the store we are copying from is the defualt Opencart store, 0 (replace 0 with the target store if different). You’ll set these Ids according to your own Opencart setup (note the following queries use the “oc_” table prefix – this may be different for your own installation):

# Associate categories
INSERT INTO oc_category_to_store (category_id, store_id)
SELECT category_id, 7 FROM oc_category_to_store WHERE store_id = 0;

# Associate information pages to the new store
INSERT INTO oc_information_to_store (information_id, store_id)
SELECT information_id, 7 FROM oc_information_to_store WHERE store_id = 0;

# Associate information pages to layouts
INSERT INTO oc_information_to_layout (information_id, store_id, layout_id)
SELECT information_id, 8, layout_id FROM oc_information_to_layout WHERE store_id = 0;

# Associate layouts
INSERT INTO oc_layout_route (layout_id , store_id, route)
SELECT layout_id , 7, route FROM oc_layout_route WHERE store_id = 0;

# Associate manufacturers
INSERT INTO oc_manufacturer_to_store (manufacturer_id, store_id)
SELECT manufacturer_id, 7 FROM oc_manufacturer_to_store WHERE store_id = 0;

# Associate products
INSERT INTO oc_product_to_store (product_id, store_id)
SELECT product_id, 7 FROM oc_product_to_store WHERE store_id = 0;

The only “gotcha” concerns a situation when the new store has a different language than the target store. i.e. the default store used English and the new store used Italian. Currently, visiting category and product pages would work, but contain no products. If this situation applies to your own store, run the following query (assume 5 is new language id – find via System > Localisation > Languages, or by looking directly at the “oc_language” table):

// For different languages if set to other than English or an existing language
INSERT INTO oc_product_description (product_id, language_id, name, description)
SELECT product_id, 5, name, description FROM oc_product_description WHERE language_id = 1;

As Opencart stores product meta data in it’s own intermediate table to allow for say a product description to vary between stores (which is essential for different languages).

That’s it – now browse the new store and see all information has correctly copied over!

Tested and working up to Opencart 1.5.6.4, but should work for any Opencart 1.5.x.x release.

When working with very large catalogues, I like to run some sanity checks. E.g. to check the product count for the existing and new store match (replace store Ids with your own as necessary):

SELECT ocp.`store_id` AS `Store Id`, IFNULL(ocs.name, 'Default') AS `Store Name`, FORMAT(COUNT(ocp.`product_id`), 0) AS `Products Count`
FROM `oc_product_to_store` ocp
LEFT JOIN `oc_store` ocs ON ocs.store_id = ocp.store_id
WHERE ocp.store_id IN(0, 7)
GROUP BY ocp.`store_id`

This query would return:

Or a quick query to verify the number of categories:

SELECT occ.`store_id` AS `Store Id`, IFNULL(ocs.name, 'Default') AS `Store Name`, FORMAT(COUNT(occ.`category_id`), 0) AS `Category Count`
FROM `oc_category_to_store` occ
LEFT JOIN `oc_store` ocs ON ocs.store_id = occ.store_id
WHERE occ.store_id IN(0, 7)
GROUP BY occ.`store_id`

For very large catalogues, I’d highly advise using Transactions if you database supports it.