Получение изображений с помощью экспорта SQL - Prestashop

ProgramBox

Получение изображений с помощью экспорта SQL — Prestashop

Post author:admin
Запись опубликована:24 августа, 2022
Post category:Вопросы по программированию

#sql #export #prestashop #export-to-csv

#sql #экспорт #prestashop #экспорт в csv

Вопрос:

Я пытаюсь экспортировать CSV-файл из Prestashop.

У меня есть код, в котором я получаю image_url для основного изображения, но я не могу получить все остальные изображения в отдельных столбцах.

Я использую этот код для заполнения URL-адреса изображения:

 CONCAT('https://',
    -- get the shop domain
    IFNULL(conf.value, 'undefined_domain'),
    -- the path to the pictures folder
    '/img/p/',
    -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
    -- assuming we have smaller image id than 100'000 ;)
    IF(CHAR_LENGTH(pi.id_image) >= 5, 
        -- if we have 5 digits for the image id
        CONCAT(
            -- take the first digit
            SUBSTRING(pi.id_image, -5, 1),
            -- add a slash
            '/'),
        ''),
    -- repeat for the next digits
    IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
    if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
    -- add the image id
    pi.id_image,
    -- put the image extension
    '.jpg') as image_url,

Мне нужно повторить его как минимум для еще 3 изображений (image2_url, image3_url и т. Д.).

Извините, я новичок в SQL export, поэтому, пожалуйста, не судите меня.

Спасибо,

Обновить

Вот код для всех данных, которые нам нужны, есть количества, описания и т.д. Не стесняйтесь использовать его:

 SELECT p.id_product, p.active, pl.name AS 'Name',
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)',
    p.price AS 'Price tax excluded or Price tax included',
    p.id_tax_rules_group AS 'Tax rules ID',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount',
    IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent',
    pr.from AS 'Discount from (yyyy-mm-dd)',
    pr.to AS 'Discount to (yyyy-mm-dd)',
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #',
    ps.name AS 'Supplier',
    pm.name AS 'Manufacturer',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.width AS 'Width',
    p.height AS 'Height',
    p.depth AS 'Depth',
    p.weight AS 'Weight',
    sa.quantity as 'Quantity',
    p.minimal_quantity AS 'Minimal quantity',
    'both' AS 'Visibility',
    p.additional_shipping_cost AS 'Additional shipping cost',
    p.unity AS 'Unity',
    p.unit_price_ratio AS 'Unit price',
    pl.description_short AS 'Short description',
    pl.description AS 'Description',
    IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)',
    pl.meta_title AS 'Meta title',
    pl.meta_keywords AS 'Meta keywords',
    pl.meta_description AS 'Meta description',
    pl.link_rewrite AS 'URL rewritten',
    pl.available_now AS 'Text when in stock',
    pl.available_later AS 'Text when backorder allowed',
    p.available_for_order AS 'Available for order (0 = No, 1 = Yes)',
    '' AS 'Product available date',
    p.date_add 'Product creation date',
    p.show_price AS 'Show price (0 = No, 1 = Yes)',
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi.id_image,
        -- put the image extension
        '.jpg') as image_url,
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi2.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi2.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi2.id_image,
        -- put the image extension
        '.jpg') as image_url2,
    0 AS 'Delete existing images (0 = No, 1 = Yes)',
    GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
    p.online_only AS 'Available online only (0 = No, 1 = Yes)',
    p.condition AS 'Cond',
    0 AS 'Customizable (0 = No, 1 = Yes)',
    0 AS 'Uploadable files (0 = No, 1 = Yes)',
    0 AS 'Text fields (0 = No, 1 = Yes)',
    p.out_of_stock as 'Out of stock',
    '1' AS 'ID',
    null AS 'Action when out of stock',
    null AS 'Depends on stock',
    null AS 'Warehouse'
FROM ps_product p

LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)

LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
GROUP BY p.id_product;

Ответ №1:

С помощью этого скрипта экспортируйте в csv, если хотите.

С уважением

 <?php
$id_lang = 1;
$id_shop = 1;

include_once('config/config.inc.php');
$link = new Link();

$listProduct = Db::getInstance()->executes('SELECT * FROM '._DB_PREFIX_.'product');

foreach ($listProduct as $product) {
    $listPictures = Db::getInstance()->ExecuteS('
            SELECT i.`cover`, i.`id_image`, i.`position`
            FROM `'._DB_PREFIX_.'image` i
            WHERE i.`id_product` = '.(int)($product['id_product']).'
            ORDER BY i.cover DESC, i.`position` ASC ');

    $prod = new Product($product['id_product'], false, $id_lang, $id_shop);

    $lign = array();
    foreach ($listPictures as $picture) {
        $ids = $product['id_product'].'-'.$picture['id_image'];
        $lign[] = $link->getImageLink($prod->link_rewrite, $ids);
    }
    echo implode(';',$lign)."n";
}

1. можно ли добавить диапазон идентификаторов? Потому что продуктов много, и на экспорт уходит много времени.

2. Неважно, мы решили эту проблему. Найдите код в вопросе

3. Отлично, не стесняйтесь решать этот вопрос, и при необходимости я доступен

Ответ №2:

Большое спасибо, ваше обновление спасло меня. И добавьте небольшую модификацию для объединения URL-адресов изображений

 SELECT p.id_product, p.active, pl.name AS 'Name',
    GROUP_CONCAT(DISTINCT(CONCAT('https://',
    -- get the shop domain
    IFNULL(conf.value, 'undefined_domain'),
    -- the path to the pictures folder
    '/img/p/',
    -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
    -- assuming we have smaller image id than 100'000 ;)
    IF(CHAR_LENGTH(pi.id_image) >= 5, 
        -- if we have 5 digits for the image id
        CONCAT(
        -- take the first digit
        SUBSTRING(pi.id_image, -5, 1),
        -- add a slash
        '/'),
        ''),
    -- repeat for the next digits
    IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
    -- add the image id
    pi.id_image,
    -- put the image extension
    '.jpg')) SEPARATOR ', ') AS 'Images (x,y,z...)',
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)',
    p.price AS 'Price tax excluded or Price tax included',
    p.id_tax_rules_group AS 'Tax rules ID',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount',
    IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent',
    pr.from AS 'Discount from (yyyy-mm-dd)',
    pr.to AS 'Discount to (yyyy-mm-dd)',
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #',
    ps.name AS 'Supplier',
    pm.name AS 'Manufacturer',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.width AS 'Width',
    p.height AS 'Height',
    p.depth AS 'Depth',
    p.weight AS 'Weight',
    sa.quantity AS 'Quantity',
    p.minimal_quantity AS 'Minimal quantity',
    'both' AS 'Visibility',
    p.additional_shipping_cost AS 'Additional shipping cost',
    p.unity AS 'Unity',
    p.unit_price_ratio AS 'Unit price',
    pl.description_short AS 'Short description',
    pl.description AS 'Description',
    IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)',
    pl.meta_title AS 'Meta title',
    pl.meta_keywords AS 'Meta keywords',
    pl.meta_description AS 'Meta description',
    pl.link_rewrite AS 'URL rewritten',
    pl.available_now AS 'Text when in stock',
    pl.available_later AS 'Text when backorder allowed',
    p.available_for_order AS 'Available for order (0 = No, 1 = Yes)',
    '' AS 'Product available date',
    p.date_add 'Product creation date',
    p.show_price AS 'Show price (0 = No, 1 = Yes)',
    --  AS image_url,
    0 AS 'Delete existing images (0 = No, 1 = Yes)',
    GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
    p.online_only AS 'Available online only (0 = No, 1 = Yes)',
    p.condition AS 'Cond',
    0 AS 'Customizable (0 = No, 1 = Yes)',
    0 AS 'Uploadable files (0 = No, 1 = Yes)',
    0 AS 'Text fields (0 = No, 1 = Yes)',
    p.out_of_stock AS 'Out of stock',
    '1' AS 'ID',
    NULL AS 'Action when out of stock',
    NULL AS 'Depends on stock',
    NULL AS 'Warehouse'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
GROUP BY p.id_product;

Ответ №3:

Вы должны использовать систему экспорта продуктов по умолчанию (Каталог -> Продукты -> Экспорт (верхний правый угол)), вы можете экспортировать весь список продуктов в CSV, используя то же самое. Этот список будет содержать только основные изображения всех продуктов. Вы можете немного изменить код, чтобы получить все изображения:

Код для того же можно найти по адресу /controllers/admin/AdminProducts.php

Метки: Получение изображений с помощью экспорта SQL — Prestashop