I need to make a feed for my site for a comparing site. It has to be a sql statement.
For now I have this:

我需要为我的网站做一个比较网站饲料。它必须是一条SQL语句。目前，我有这样的想法：

select  pl.name as Titel,
    ROUND(p.price*1.21,2) as Price,
    replace(concat('http://', ifnull(conf.value,'domain/'), cl.name, '/', p.id_product, '-' , pl.name, '.html'),' ','-') as Link,
     concat('http://', ifnull(conf.value,'domain'), '/img/p/', p.id_product, '-' , pi.id_image, '.jpg') as "Image-location",
     cl.name as Categorie,
    p.id_product AS ID
from dbrb_product p
left join dbrb_image pi on p.id_product = pi.id_product
left join dbrb_product_lang pl on p.id_product = pl.id_product
left join dbrb_category_lang cl on p.id_category_default = cl.id_category
left join dbrb_configuration conf on conf.name = 'dbrb_SHOP_DOMAIN'
left join dbrb_product_carrier x on p.id_product = x.id_product
group by p.id_product

But now with the new prestashop version 1.6 the image doesn't work anymore.

但现在，随着新的prestashop 1.6版的发布，这个镜像不再起作用了。

Now the image path is: domain.com/img/p/number/number/number/image.png
I don't get the logic from it, can somebody tell me?

现在图像路径是：domain.com/img/p/number/number/number/image.png我不明白其中的逻辑，有人能告诉我吗？

There is also another problem I have to deal with, because there are some products which have the same image.

我还有一个问题要处理，因为有些产品的形象是一样的。

Can somebody complete the SQL code or help me further?

有人可以完成SQL代码或进一步帮助我吗？

Thanks!

谢谢！

Here is the SQL code to get all possible product details including correct urls of the images:

以下是获取所有可能的产品详细信息的SQL代码，包括图像的正确URL：

SELECT p.id_product AS 'ID',
pl.id_lang AS 'ID_LANG',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
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',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition',
concat( 'http://YOUR-URL.com/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 1=1
and p.active = 1

Is simple, replace concat from your query for this one:

很简单，请将查询中的Conat替换为以下内容：

concat('http://', ifnull(conf.value,'example.com'), '/img/p/',SUBSTRING(pi.id_image from -4 FOR 1),'/',SUBSTRING(pi.id_image from -3 FOR 1),'/',SUBSTRING(pi.id_image from -2 FOR 1),'/',SUBSTRING(pi.id_image from -1 FOR 1),'/' , pi.id_image, '.jpg') as product_image,

Concat(‘http://’，ifNULL(conf.Value，‘Example.com’)，‘/img/p/’，SUBSTRING(pi.id_Image from-4 for 1)，‘/’，SUBSTRING(pi.id_Image from-3 for 1)，‘/’，SUBSTRING(pi.id_Image from-2 for 1)，‘/’，SUBSTRING(pi.id_Image from-1 for 1)，‘/’，pi.id_Image，‘.jpg’)as PRODUCT_IMAGE，

The number in the paths to an image is the digits of its ID, for example image with ID 121 will have the following path:

图像路径中的数字是其ID的数字，例如ID为121的图像将具有以下路径：

http://domain.com/img/p/1/2/1/121.jpg

However, MySQL doesn't have any built-in functions to do this (AFAIK), so you will need to build a user-defined function.

但是，MySQL没有任何内置函数(AFAIK)来执行此操作，因此您需要构建一个用户定义的函数。

Mh, im using this:
concat('http://', ifnull(conf.value,'example.com'), '/img/c/', c.id_category, '.jpg') as url_image,

MH，IM Using This：Concat(‘http://’，ifNull(conf.Value，‘Example.com’)，‘/img/c/’，c.id_ategory，‘.jpg’)as url_Image，

it works perfect.

它工作得很完美。

The image path is based on the image id. You split all the digits and add a slash between them to get the folder where the image is stored.

图像路径基于图像ID。您可以拆分所有数字，并在它们之间添加一个斜杠，以获得存储图像的文件夹。

-- build the image path
CONCAT('http://',
        -- 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

Use the prestashop class "IMAGE" instead of loading MySql with functions

使用prestashop类“IMAGE”，而不是使用函数加载MySQL

public static function getImgFolderStatic($id_image)

Returns the path to the folder containing the image in the new filesystem

返回新文件系统中包含图像的文件夹的路径

It strips the number of the image into the subfolders used by prestashop.

它将图像的编号剥离到prestashop使用的子文件夹中。

Example:

示例：

foreach($SQLresult as $key=>$value)
   {
      $imageUrl=_PS_PROD_IMG_DIR_.Image::getImgFolderStatic($value['id_image']).$value['id_image'].".jpg"
   }

Here $productID is your product's ID :)

其中$ProductID是您的产品ID：)

$prod = new Product($productID);
$imgArray = $prod->getImages('1');
if (count($imgArray)>0) {
    $imgID = $imgArray[0]["id_image"];
    $imageUrl=_THEME_PROD_DIR_.Image::getImgFolderStatic($imgID).$imgID.".jpg";
}

Building on the previous answer from SunnyBoy, I have written this, which also generates the friendly URL for the product (I have tested it with PrestaShop 8.1.1):

基于SunnyBoy之前的回答，我写了这个，它也为产品生成了友好的URL（我已经用PrestaShop 8.1.1测试过了）：

SELECT 
    p.id_product AS 'ID',
    pl.id_lang AS 'ID_LANG',
    p.active AS 'Active (0/1)',
    pl.name AS 'Name',
    p.id_category_default AS 'category_id',
    cl.link_rewrite AS 'category_urlslug',
    concat( 'https://example.com/', cl.link_rewrite, '/', p.id_product, '-', pl.link_rewrite, '.html') AS 'product_url',
    p.price AS 'Price tax excl.',
    p.id_tax_rules_group AS 'Tax rules ID',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    p.reference AS 'Reference #',
    p.quantity AS 'Quantity',
    pl.description_short AS 'Short description',
    pl.description AS 'Description',
    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',
    p.date_add AS 'Product creation date',
    p.show_price AS 'Show price',
    p.online_only AS 'Available online only',
    p.condition AS 'Condition',
    concat('https://example.com/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg') AS 'image_url'
FROM ps_product p
    INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
    INNER JOIN ps_category_lang cl ON p.id_category_default = cl.id_category
    LEFT JOIN ps_image im ON p.id_product = im.id_product AND im.cover = 1
and p.active = 1;