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;