2022-04-24
52334
22
6330
6 minutes.


Downloading a product catalog via E-Trade HTTP Tunnel for self-written CMS (SQL queries)

The E-Trade HTTP Tunnel integration module is an application designed for the simple interaction of E-Trade Jumper with the database of an online store (website).

The module is developed in the PHP programming language, MySQL DBMS is supported as the site database. To configure and connect the module, you will need to perform the following steps:

  • Transfer files with the module to the hosting account of your site, for example, using FTP.
  • Select your site's CMS from the list available in E-Trade Jumper.
  • Enter a link to your site in E-Trade Jumper and click the "Connect" button.

We can set up integration with your site, for this, send us an email. mail access to your site via FTP, this is enough for configuration.

Installing the E-Trade Tunnel integration module on your hosting

  1. Download the module from this link extract the contents of the etrade_http_tunnel2.zip archive to a temporary folder on your local PC. The archive contains directories and files
    Loading a product catalog via E-Trade HTTP Tunnel for self-written CMS (SQL queries) - E-Trade Jumper from ElbuzGroup dropshipping suppliers aliexpress amazon shopify best beginners apps products ebay wix distributors how to start business vendors stores alibaba compares your prices orders for suppliers create catalog
  2. Rewrite the contents of the unpacked archive to the root folder of the installed site (online store), along with the etrade_http_tunnel2 folder. This can be done, for example, using the FileZilla FTP client .

Setting up the E-Trade Tunnel integration module to work with the website database of an individual CMS (self-written)

To download a catalog of products from the site, open the "Download products to the catalog" window by clicking on the shortcut on the desktop
elbuz etrade jumper import products data tunnel
Or select a menu item
elbuz etrade jumper import products data tunnel

Next, select the E-Trade Tunnel data format, open the "CMS Selection" tab, select the "Manual mode (SQL queries)" item in the drop-down list, specify the address of your site
elbuz etrade jumper import data tunnel
Open the "Access Settings" tab, specify a link to the E-Trade Tunnel module, login and password, and also specify the parameters for accessing the site database
elbuz etrade jumper import data tunnel

Open the "Select CMS" tab, check the connection
elbuz etrade jumper import data tunnel
After the connection is successfully established, you need to register SQL queries to get data from the site, open the "Download" tab, click the "Configure SQL queries" button
elbuz etrade jumper import data tunnel

Compose SQL queries based on the structure of your database
elbuz etrade jumper import data tunnel

To obtain data from the site, it is necessary to create separate SQL queries for categories, products, manufacturers, attribute blocks, attributes, and product attribute values.
Each SQL query specifies the data type, for example, to get the SQL categories, the query starts like this
SELECT 'category' as row_type
In each data type, the order of the fields must be strictly observed. If there is no field in your database, just specify it as an empty stub - "" as language_code
SQL queries end with three semicolons - ;;;

SELECT 'category' as row_type, category_uuid, category_uuid_parent, language_code, category_id, parent_id, sort_order, status, image, seo_url, product_count, name, description_short, description_full, meta_title, meta_description, meta_keyword, h1, image_url FROM category c GROUP BY category_id, language_code;;; SELECT 'product' as row_type, product_uuid, category_uuid, language_code, manufacturer_uuid, product_id, category_id, manufacturer_id, status, price, price_rrp, price_old, quantity, stock_status, manufacturer_name, name, description_short, description_full, meta_title, meta_description, meta_keyword, h1, tag, model, seo_url, sort_order, sku, upc, ean, jan, isbn, mpn, location, points, date_available, weight, length, width, height, warranty, subtract, minimum, viewed, date_added, date_modified, flag_xml_export, flag_popular, flag_best_deal, flag_fix_price, sku_supply, delivery_cost, ext_url, flag_new_product, <code> type_id,
product_uuid_parent,
FROM product
GROUP BY product_id, language_code;;;

SELECT 'product_category' as row_type,
product_uuid,
category_uuid,
product_id,
category_id,
main_category,
sort_order
FROM product_to_category
GROUP BY product_id, category_id;;;

SELECT 'product_image' as row_type,
product_uuid,
product_id,
image_file_name,
image_url,
sort_order,
image_uuid
FROM image

SELECT 'manufacturer' as row_type,
manufacturer_uuid,
manufacturer_id,
language_code,
sort_order,
status,
name,
image,
meta_title,
meta_description,
meta_keywords,
url,
seo_url,
warranty,
seo_h1,
description,
manufacturer_address,
manufacturer_service,
image_url
FROM manufacturer;;;

SELECT 'attribute_block' as row_type,
attribute_block_uuid,
attribute_block_id,
language_code,
name,
sort_order,
image,
description
FROM attribute_group
GROUP BY attribute_block_id, language_code;;;

SELECT 'attribute' as row_type,
attribute_uuid,
attribute_block_uuid,
language_code,
name,
attribute_id,
attribute_block_id,
sort_order,
image,
attribute_type,
required,
code,
flag_option,
flag_multiline,
show_on_page,
flag_expand,
status,
description
FROM attribute a
GROUP BY attribute_id, language_code;;;

SELECT 'product_attribute_lang' as row_type,
product_uuid,
attribute_uuid,
language_code,
product_id,
attribute_id,
product_attribute_value,
attribute_image,
0 as flag_multiline
FROM product_attribute
GROUP BY product_id, attribute_id, language_code;;;

Description of types

Field name Data type Description
row_type Line Fixed "category"
category_uuid Line The unique symbolic identifier of the category. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
category_uuid_parent Line The unique symbolic identifier of the parent category. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the parent_id field. The field is required.
language_code Line Language code, for example ru. If the site stores all categories for one language, then leave this field blank.
category_id Number Category unique numeric ID
parent_id Number Unique numeric ID of the parent category
sort_order Number Sorting order
status Number 0 or 1 Activity status
image Line Image file name
seo_url Line SEO category (no website address)
product_count Number Number of products in a category
name Line name of category
description_short Line Short description
description_full Line Full description
meta_title Line SEO meta header
meta_description Line SEO meta description
meta_keyword Line SEO meta keywords
h1 Line The h1 tag (if, in addition to the category name, there is also a separate name for the h1 tag)
image_url Line Link to the category image (where you can download the image)
Field name Data type Description
row_type Line Fixed "product"
product_uuid Line The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
category_uuid Line The unique symbolic identifier of the category. The main category for the product. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
language_code Line Language code, for example ru. If the site stores all products for one language, then leave this field blank.
manufacturer_uuid Line Manufacturer's unique symbolic identifier. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the manufacturer_id field.
product_id Number Unique numeric product identifier
category_id Number Category unique numeric ID
manufacturer_id Number Manufacturer's unique numeric identifier
status Number 0 or 1
price Number The price of the product. Decimal separator point.
price_rrp Number RRP - the recommended retail price of the goods. Decimal separator point.
price_old Number Old item price
quantity Number Quantity of goods
stock_status Line The symbolic status of the product, for example, Yes, No, On order.
manufacturer_name Line Manufacturer's name
name Line Name of product
description_short Line Brief product description
description_full Line Full product description
meta_title Line SEO meta header
meta_description Line SEO meta description
meta_keyword Line SEO meta keywords
h1 Line The h1 tag (if, in addition to the title, there is also a separate text for the h1 tag)
tag Line Tag List
model Line Item Model
seo_url Line SEO product (no website address)
sort_order Number Sorting order
sku Line Internal item number
upc Line UPC barcode
ean Line Barcode EAN
Barcode JAN Line
isbn Line Barcode ISBN
mpn Line Manufacturer's SKU
location Line Warehouse location
points Number Number of points when ordering goods
date_available Line Product Availability Date
weight Number Item weight
length Number Item length
width Number Width
height Number Height
warranty Line Warranty
subtract Number 0 or 1 Subtract quantity from stock when ordering
minimum Number 0 or 1 Minimum available order quantity
viewed Number Number of product views
date_added Line Date the product was added
date_modified Line Item modification date
flag_xml_export Number 0 or 1 XML upload flag (marketplace)
flag_popular Number 0 or 1 Flag - Popular Item
flag_best_deal Number 0 or 1 Flag - Best Buy
flag_fix_price Number 0 or 1 Flag - Fixed price
sku_supply Line Article of the counterparty (supplier)
delivery_cost Number Cost of delivery
ext_url Line External link to the product on the site
flag_new_product Number 0 or 1 Flag - New Item (new)
type_id Number Product type. 1 - regular product, 2 - option product.
product_uuid_parent Line The unique symbolic identifier of the parent product. If there is no such identifier in the site database table, then specify a unique numeric identifier from the parent product, for example, from the product_id field.

List of links to product categories, for example 1 product can have several links to other categories

Field name Data type Description
row_type Line Fixed "product_category"
product_uuid Line The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
category_uuid Line The unique symbolic identifier of the category. The main category for the product. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
product_id Number Unique numeric product identifier
category_id Number Category unique numeric ID
main_category Number 0 or 1 The flag is the main product category
sort_order Number Sorting order
Field name Data type Description
row_type Line Fixed "product_image"
product_uuid Line The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
product_id Number Unique numeric product identifier
image_file_name Line Product photo file name
image_url Line Link to the photo of the product (where you can download the photo)
sort_order Number Sorting order
image_uuid Line The unique symbolic ID of the link to the photo.
Field name Data type Description
row_type Line Fixed "manufacturer"
manufacturer_uuid Line Unique symbolic identifier of the product manufacturer. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the manufacturer_id field. The field is required.
manufacturer_id Number Manufacturer's unique numeric identifier
language_code Line Language code, for example ru. If the site stores all manufacturers for the same language, then leave this field blank.
sort_order Number Sorting order
status Number 0 or 1 Activity status
name Line Manufacturer's name
image Line Product photo file name
meta_title Line SEO meta header
meta_description Line SEO meta description
meta_keywords Line SEO meta keywords
url Line Link to the manufacturer's website
seo_url Line SEO manufacturer (no website address)
warranty Line Manufacturer's Warranty
seo_h1 Line The h1 tag (if, in addition to the title, there is also a separate text for the h1 tag)
description Line Manufacturer's Description
manufacturer_address Line Manufacturer's address
manufacturer_service Line List of manufacturer's service centers
image_url Line Link to the photo of the product (where you can download the photo)
Field name Data type Description
row_type Line Fixed "attribute_block"
attribute_block_uuid Line The unique symbolic identifier of the attribute block. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_block_id field. The field is required.
attribute_block_id Number Unique numeric attribute block identifier
language_code Line Language code, for example ru. If the site stores all attribute blocks for the same language, then leave this field blank.
name Line Attribute block name
sort_order Number Sorting order
image Line Attribute Block Photo File Name
description Line Description of the attribute block
Field name Data type Description
row_type Line Fixed "attribute"
attribute_uuid Line The unique character identifier of the attribute. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
attribute_group_uuid Line The unique character identifier of the attribute block. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the attribute_block_id field
language_code Line Language code, for example ru. If the site stores all attributes for one language, then leave this field blank.
name Line Attribute name
attribute_id Number Attribute unique numeric ID
attribute_block_id Number Unique numeric attribute block identifier
sort_order Number Sorting order
image Line Attribute Block Photo File Name
attribute_type Line Attribute type. Available values: select, checkbox, radio, slider, image, datetime, text, text_area, decimal, float
required Number 0 or 1 Flag - required
code Line Symbolic Attribute Code
flag_option Number 0 or 1 Flag - Item Option
flag_multiline Number 0 or 1 Flag - Multi-string values
show_on_page Number 0 or 1 Flag - show in the product card
flag_expand Number 0 or 1 Flag - When filtering products, the list of values is expanded
status Number 0 or 1 Attribute Activity
description Line Attribute description
Field name Data type Description
row_type Line Fixed "product_attribute_lang"
product_uuid Line The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
attribute_uuid Line The unique character identifier of the attribute. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
language_code Line Language code, for example ru. If the site stores all attributes for one language, then leave this field blank.
product_id Number Unique numeric product identifier
attribute_id Number Attribute unique numeric ID
product_attribute_value Line Attribute value
sort_order Number Sorting order
attribute_image Line Attribute value photo file name
flag_multiline Number 0 or 1 Flag - Multi-string values


hotline parser



Save a link to this article


Next