Products Table
DROP TABLE IF EXISTS `_mid_products`; CREATE TABLE IF NOT EXISTS `_mid_products` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `active_0_1` text DEFAULT NULL, `name` text DEFAULT NULL, `categories_cs` text DEFAULT NULL, `price_tax_excluded` text DEFAULT NULL, `tax_rules_id` text DEFAULT NULL, `wholesale_price` text DEFAULT NULL, `on_sale_0_1` text DEFAULT NULL, `discount_amount` text DEFAULT NULL, `discount_percent` text DEFAULT NULL, `discount_from_yyyy_mm_dd` text DEFAULT NULL, `discount_to_yyyy_mm_dd` text DEFAULT NULL, `reference_num` text DEFAULT NULL, `supplier_reference_num` text DEFAULT NULL, `supplier` text DEFAULT NULL, `manufacturer` text DEFAULT NULL, `ean13` text DEFAULT NULL, `upc` text DEFAULT NULL, `ecotax` text DEFAULT NULL, `width` text DEFAULT NULL, `height` text DEFAULT NULL, `depth` text DEFAULT NULL, `weight` text DEFAULT NULL, `delivery_time_of_in_stock_products` text DEFAULT NULL, `delivery_time_of_out_of_stock_products_with_allowed_orders` text DEFAULT NULL, `quantity` text DEFAULT NULL, `minimal_quantity` text DEFAULT NULL, `low_stock_level` text DEFAULT NULL, `send_me_an_email_when_the_quantity_is_under_this_level` text DEFAULT NULL, `visibility` text DEFAULT NULL, `additional_shipping_cost` text DEFAULT NULL, `unity` text DEFAULT NULL, `unit_price` text DEFAULT NULL, `summary` text DEFAULT NULL, `description` text DEFAULT NULL, `tags_cs` text DEFAULT NULL, `meta_title` varchar(255) DEFAULT NULL, `meta_keywords` varchar(255) DEFAULT NULL, `meta_description` text DEFAULT NULL, `url_rewritten` text DEFAULT NULL, `text_when_in_stock` text DEFAULT NULL, `text_when_backorder_allowed` text DEFAULT NULL, `available_for_order_0_1` text DEFAULT NULL, `product_available_date` text DEFAULT NULL, `product_creation_date` text DEFAULT NULL, `show_price_0_1` text DEFAULT NULL, `image_urls_cs` text DEFAULT NULL, `image_alt_texts_cs` text DEFAULT NULL, `delete_existing_images_0_1` text DEFAULT NULL, `feature_cs` text DEFAULT NULL, `available_online_only_0_1` text DEFAULT NULL, `condition_` text DEFAULT NULL, `customizable_0_1` text DEFAULT NULL, `uploadable_files_0_1` text DEFAULT NULL, `text_fields_0_1` text DEFAULT NULL, `out_of_stock_action` text DEFAULT NULL, `virtual_product` text DEFAULT NULL, `file_url` text DEFAULT NULL, `number_of_allowed_downloads` text DEFAULT NULL, `expiration_date` text DEFAULT NULL, `number_of_days` text DEFAULT NULL, `id_name_of_shop` text DEFAULT NULL, `advanced_stock_management` text DEFAULT NULL, `depends_on_stock` text DEFAULT NULL, `warehouse` text DEFAULT NULL, `acessories_cs` text DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Categories table
DROP TABLE IF EXISTS `_mid_categories`; CREATE TABLE IF NOT EXISTS `_mid_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `active` int(11) NOT NULL DEFAULT 1, `name` text DEFAULT NULL, `parent_category` text DEFAULT NULL, `root_category` text DEFAULT NULL, `description` text DEFAULT NULL, `meta_title` text DEFAULT NULL, `meta_keyword` varchar(255) DEFAULT NULL, `meta_description` text DEFAULT NULL, `url_rewritten` text DEFAULT NULL, `image` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT;
SQLs
fill the categories table:
insert into _mid_categories (id,active,parent_category,description,name,meta_title,meta_keyword,meta_description,url_rewritten) SELECT (categoryid+3) as id,catvisible as active,(catparentid+3) as `parent_category`,catdesc as description,catname as meta_title,catname as `name`,catmetakeywords as meta_keyword,catmetadesc as meta_description,replace(catname,' ','-') as url_rewritten FROM isc_categories ORDER BY catsort; UPDATE `_mid_categories` set root_category=1 WHERE parent_category='0'; UPDATE `_mid_categories` set root_category=0 WHERE parent_category!='0'; UPDATE _mid_categories targetTable LEFT JOIN _mid_categories sourceTable ON targetTable.parent_category=sourceTable.id SET targetTable.parent_category=sourceTable.id; UPDATE `_mid_categories` SET parent_category='2' WHERE parent_category is null;
And for exporting it to csv you have to use this query:
SELECT mc.* FROM `_mid_categories` as mc order by CASE WHEN parent_category>id THEN cast(id as unsigned) ELSE cast(parent_category as unsigned) END ASC,id asc
this fill the products table:
truncate _mid_products; insert into _mid_products (`product_id`, `active_0_1`, `name`, `categories_cs`, `price_tax_excluded`, `tax_rules_id`, `wholesale_price`, `on_sale_0_1`, `discount_amount`, `discount_percent`, `discount_from_yyyy_mm_dd`, `discount_to_yyyy_mm_dd`, `reference_num`, `supplier_reference_num`, `supplier`, `manufacturer`, `ean13`, `upc`, `ecotax`, `width`, `height`, `depth`, `weight`, `delivery_time_of_in_stock_products`, `delivery_time_of_out_of_stock_products_with_allowed_orders`, `quantity`, `minimal_quantity`, `low_stock_level`, `send_me_an_email_when_the_quantity_is_under_this_level`, `visibility`, `additional_shipping_cost`, `unity`, `unit_price`, `summary`, `description`, `tags_cs`, `meta_title`, `meta_keywords`, `meta_description`, `url_rewritten`, `text_when_in_stock`, `text_when_backorder_allowed`, `available_for_order_0_1`, `product_available_date`, `product_creation_date`, `show_price_0_1`, `image_urls_cs`, `image_alt_texts_cs`, `delete_existing_images_0_1`, `feature_cs`, `available_online_only_0_1`, `condition_`, `customizable_0_1`, `uploadable_files_0_1`, `text_fields_0_1`, `out_of_stock_action`, `virtual_product`, `file_url`, `number_of_allowed_downloads`, `expiration_date`, `number_of_days`, `id_name_of_shop`, `advanced_stock_management`, `depends_on_stock`, `warehouse`, `acessories_cs`) SELECT p.productid as `product_id`, p.prodvisible as `active_0_1`, replace(p.prodname,'#','No ') as `name`, GROUP_CONCAT(DISTINCT (cats.categoryid+3) ORDER BY cats.categoryid asc SEPARATOR ',') as `categories_cs`, (p.prodprice/1.2) as `price_tax_excluded`, '1' as `tax_rules_id`, p.prodprice as `wholesale_price`, '0' as `on_sale_0_1`, null as `discount_amount`, null as `discount_percent`, null as `discount_from_yyyy_mm_dd`, null as `discount_to_yyyy_mm_dd`, p.prodcode as `reference_num`, null `supplier_reference_num`, null `supplier`, brands.brandname as `manufacturer`, null as `ean13`, null as `upc`, null as `ecotax`, null as `width`, null as `height`, null as `depth`, p.prodweight as `weight`, null as `delivery_time_of_in_stock_products`, null as `delivery_time_of_out_of_stock_products_with_allowed_orders`, '999999' as `quantity`, null as `minimal_quantity`, null as `low_stock_level`, null as `send_me_an_email_when_the_quantity_is_under_this_level`, 'both' as `visibility`, null as `additional_shipping_cost`, null as `unity`, null as `unit_price`, SUBSTRING(p.prodmetadesc,0,800) as `summary`, p.proddesc as `description`, null as `tags_cs`, p.prodname as `meta_title`, p.prodmetakeywords as `meta_keywords`, SUBSTRING(p.prodmetadesc,0,512) as `meta_description`, replace(p.prodname,' ','_') as `url_rewritten`, null as `text_when_in_stock`, null as `text_when_backorder_allowed`, p.prodallowpurchases as `available_for_order_0_1`, DATE_FORMAT(FROM_UNIXTIME(p.proddateadded), '%Y-%m-%d') AS `product_available_date`, DATE_FORMAT(FROM_UNIXTIME(p.proddateadded), '%Y-%m-%d') AS `product_creation_date`, '1' as `show_price_0_1`, GROUP_CONCAT(DISTINCT CONCAT('https://www.natamno.com/product_images/',imgs.imagefile) ORDER BY imgs.imagesort asc SEPARATOR ',') as `image_urls_cs`, null as `image_alt_texts_cs`, '0' as `delete_existing_images_0_1`, null as `feature_cs`, '0' as `available_online_only_0_1`, LOWER(p.prodcondition) as `condition_`, '0' as `customizable_0_1`, '0' as `uploadable_files_0_1`, '0' as `text_fields_0_1`, null as `out_of_stock_action`, null as `virtual_product`, null as `file_url`, null as `number_of_allowed_downloads`, null as `expiration_date`, null as `number_of_days`, '1' as `id_name_of_shop`, null as `advanced_stock_management`, null as `depends_on_stock`, null as `warehouse`, null as `acessories_cs` FROM isc_products as p LEFT JOIN isc_brands as brands ON p.prodbrandid=brands.brandid LEFT JOIN isc_categoryassociations as cats ON p.productid=cats.productid LEFT JOIN (SELECT * FROM isc_product_images where imagefile NOT like '%_tiny.%' AND ((REPLACE(imagefile,'_thumb.','.')!=imagefile AND REPLACE(imagefile,'_thumb.','.') NOT IN (SELECT imagefile FROM isc_product_images) ) OR REPLACE(imagefile,'_thumb.','.')=imagefile ) ) as imgs ON p.productid=imgs.imageprodid /*WHERE p.productid=9096*/ group by p.productid; UPDATE `_mid_products` SET meta_title=REPLACE(meta_title,'>',' '); UPDATE `_mid_products` SET meta_title=REPLACE(meta_title,'<',' '); UPDATE `_mid_products` SET meta_description=REPLACE(meta_description,'>',' '); UPDATE `_mid_products` SET meta_description=REPLACE(meta_description,'<',' '); UPDATE `_mid_products` SET meta_keywords=REPLACE(meta_keywords,'>',' '); UPDATE `_mid_products` SET meta_keywords=REPLACE(meta_keywords,'<',' '); UPDATE `_mid_products` SET `name`=REPLACE(`name`,'>',' '); UPDATE `_mid_products` SET `name`=REPLACE(`name`,'<',' '); UPDATE _mid_products SET meta_description=SUBSTRING(meta_description,0,512);
Export it to CSV by just using phpMyAdmin – export function – CSV
Use this query to Export Combinations
SELECT pvc.vcproductid as product_id, '' as productreference, GROUP_CONCAT(CONCAT(CONCAT(pvo.voname,'-',vovariationid,'-',REPLACE(REPLACE(pv.vname,':','-'),',','-')),':select:',pvo.vovaluesort) ORDER BY pvo.vovaluesort asc SEPARATOR ',' ) as attribute, GROUP_CONCAT(CONCAT((case when pvo.vovalue='' then '-' else pvo.vovalue end),':',pvo.vooptionsort) ORDER BY pvo.vovaluesort asc SEPARATOR ',' ) as val, CASE WHEN pvc.vcenabled=1 THEN '999999' ELSE 0 END as qty FROM isc_product_variation_combinations pvc LEFT JOIN isc_product_variation_options pvo ON pvo.vovariationid=pvc.vcvariationid AND FIND_IN_SET(pvo.voptionid,pvc.vcoptionids) LEFT JOIN isc_product_variations pv ON pvc.vcvariationid=pv.variationid /*WHERE pvc.vcproductid=9096*/ WHERE pvc.vcproductid>0 GROUP BY pvc.combinationid;