Get image url in prestashop

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:

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.

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?

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?

Thanks!

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:

[To see links please register here]

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

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

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,

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

it works perfect.

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

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.

Example:


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

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.

-- 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

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

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

Here $productID is your product's 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";
}