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;
