WooCommerce on a WordPress Multisite

Question: Having established a WordPress Multisite network and subsequently integrated the WooCommerce plugin to oversee the e-commerce functionalities across the network, there arises a requirement to synchronize the WooCommerce database of the main site with one of its associated sub-sites. The objective is to enable the sub-sites to access and display the product categories and attributes originating from the main site.

Specifically, when attempting to add a new product on a sub-site, the default behavior is for product categories and attributes to be sourced from the current sub-site. However, the desired outcome is to retrieve this pertinent information from the main site.

In light of this, the inquiry is posed:

What procedures should be undertaken to facilitate the connection of the WooCommerce database of the main site with a designated sub-site, ensuring that product categories and attributes are retrieved from the main site when adding a product on the sub-site?

It is worth noting that a potential approach involves extracting relevant data from tables such as wp_terms and wp_woocommerce_attributes_taxonomies. Nonetheless, a comprehensive and methodical explanation or solution is sought for the aforementioned database synchronization requirement.

Answer: To share WooCommerce product categories and attributes across the main site and its sub-sites in a WordPress Multisite network, you can follow these general steps. Please note that modifying the database directly requires caution, and it's always recommended to have a backup before making changes.

1. Identify Main Site Terms:

• Identify the term IDs for the product categories and attributes on the main site by querying the wp_terms and wp_woocommerce_attribute_taxonomies tables.

sqlCopy code
SELECT term_id, name FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy = 'product_cat');

This query will give you the term IDs and names of the product categories on the main site. Repeat a similar query for product attributes.

sqlCopy code
SELECT * FROM wp_woocommerce_attribute_taxonomies;

2. Update Sub-Site Tables:

• For each sub-site where you want to import the categories and attributes from the main site, you'll need to insert records into the wp_terms, wp_term_taxonomy, and wp_woocommerce_attribute_taxonomies tables.

sqlCopy code
-- Insert product categories
INSERT INTO subsite_database.wp_terms (term_id, name, slug, term_group) VALUES (main_site_term_id, 'Category Name', 'category-slug', 0);
INSERT INTO subsite_database.wp_term_taxonomy (term_taxonomy_id, term_id, taxonomy, description, parent, count) VALUES (main_site_term_id, main_site_term_id, 'product_cat', '', 0, 0);

-- Insert product attributes
INSERT INTO subsite_database.wp_woocommerce_attribute_taxonomies (attribute_id, attribute_name, attribute_label, attribute_type, attribute_orderby, attribute_public) VALUES (main_site_attribute_id, 'attribute_name', 'Attribute Label', 'select', 'menu_order', 1);

Replace subsite_database with the actual database name of your sub-site.

3. Update Product Relationships:

• After inserting the terms and attributes, you'll need to associate the products on the sub-site with the imported categories and attributes. This involves updating the wp_term_relationships and wp_term_taxonomy tables.

sqlCopy code
-- Update product-category relationships
INSERT INTO subsite_database.wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT object_id, main_site_term_id, 0 FROM main_site_database.wp_term_relationships WHERE term_taxonomy_id = main_site_term_id;

-- Update product-attribute relationships
INSERT INTO subsite_database.wp_woocommerce_attribute_relationships (attribute_id, object_id) SELECT main_site_attribute_id, object_id FROM main_site_database.wp_woocommerce_attribute_relationships WHERE attribute_id = main_site_attribute_id;

Replace main_site_database with the actual database name of your main site.

Remember to adjust the queries based on your actual database structure, and always test changes in a safe environment first. Additionally, this approach assumes a certain level of understanding of MySQL and the WordPress database schema. If you're not comfortable with direct database manipulation, consider seeking assistance from a developer or using a plugin that offers this functionality.