Hola,
En esta entrada vamos a ver cómo consultar todas las imágenes y todos los datos de todos los productos que tenemos en prestashop.
A continuación dejo la query con la que puedes consultar todos los datos.
Está query la he lanzado y probado sobre una versión de PrestaShop 1.7.
SELECT p.id_product, p.active, pl.name AS 'Name',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z…)',
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;La tendrías que lanzar desde MySQL o desde consola y te debe de mostrar un registro por cada uno de los productos que tengas así como las imágenes que tienes.