user2874583 user2874583 - 11 days ago 5
PHP Question

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!

Answer

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,

Comments