user2874583 user2874583 - 1 year ago 168
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 as Titel,
ROUND(p.price*1.21,2) as Price,
replace(concat('http://', ifnull(conf.value,'domain/'),, '/', p.id_product, '-' ,, '.html'),' ','-') as Link,
concat('http://', ifnull(conf.value,'domain'), '/img/p/', p.id_product, '-' , pi.id_image, '.jpg') as "Image-location", 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 = '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:
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?


Answer Source

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

concat('http://', ifnull(conf.value,''), '/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,

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download