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, 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(, '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:

Store Id Store Name Products Count
0 Default 97.274
7 Italian Store 97,274

Or a quick query to verify the number of categories:

SELECT occ.`store_id` AS `Store Id`, IFNULL(, '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`
Store Id Store Name Category Count
0 Default 23
7 Italian Store 23

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

Published by

Rob Allport

Web Developer based in Stoke-on-Trent Staffordshire Google+ - Twitter

9 thoughts on “Opencart 1.5.x Multistore – Associating Existing Data”

  1. Thank you for this post. I looked high and low for an explanation of this and didn’t like the sound of paying £60 for an extension!!!! I finded a similar post on the verified opencart forums but the query didn’t link all my old products to the new store. I’ll also set your “sanity” sql queries to run on a loop and email me of the counts are different to the default opencart store

    1. Cheers for the comment 🙂

      For the “sanity check” as I phrased them, they are only useful at the time of the import – to verify all products have been copied across. Afterwards, when the store is being managed day to day, I;d imagine these counts would change. Unless you’re saying that products/categories/pages/manufacturers etc. will never change, running some sort of daily cron job will be pretty fruitless 🙂

  2. Opencart should have this functionality built in as standard. The fact a developer would have the hack the database to link products across a store is a failing of Opencart in my eyes. Use Magento people – it’s much better.

    1. Cheers for the comment 🙂

      I tend to agree with you, as I’ve had to use the multistore feature a few times. As mentioned in the article, Opencart makes the fairly reasonable assumption that all data will be store specific – that will work for the majority of cases. I wouldn’t call running a few basic sql statements a “hack” – I could easily knock up a small VQMod, with a nice form and menu item in the admin area that achieved exactly this.

      Magento and Opencart both have their uses imo and I think it’s illy to say one is better than the other. I’m sure Magento has this exact scenario planned out – I’ll have a look around as I’ve never used the multi store feature when using Magento.

    2. What the hell is this obsession with the bloated Magento? Whilst I’ve not used Opencart before, the fact Opencart don;t try to account for every bloody user case is good. If Opencart did, then they’d end up with a bloated solution like Magento.

  3. Opencart should take this into account? Debatable certainly. Opencart should make the user aware of this huge assumption in their admin area – certainly! I imagine a lot of people create a multi store and quickly say “wtf, where have all my products gone!”.

  4. [rant]
    I find your blog very odd. YOu seem to write about Laravel 4 and PHP standards which is good. I enjoy reading about those subjects. Then I see you write about possibly the worst written piece of software ever, ala Opencart. The low quality of their codebase makes me very sad. Not sure why anyone would use it, especialloy someone who uses Laravel.

    1. ok 🙂 I’m not going to sit here and say Opencart is the most perfect piece of PHP code you’ll ever see (some of the repetition in controllers and views annoys the hell out of me), because it clearly isn’t. What it is to me, is a platform to get medium sized stores up and running without a load of hassle. Customers also seem to completely dig the Opencart admin area too, which is great. I get a lot less questions about how to I do x, y and z from customers with Opencart admin areas.

Leave a Reply

Your email address will not be published. Required fields are marked *