Export with SQL Manager, problem with links.

[semaprint]

Hello,

 

I need export my products to CSV. I use SQL Manager and this query:

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
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_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 6
AND cl.id_lang = 6
AND p.id_shop_default = 1 AND c.id_shop_default = 1
GROUP BY p.id_product

It works but i need link to image too.

How Can I modify this query to get image urls?

 

 

 

[onno1000]

EDIT: I can't past the code here?? It will leave excatly the image part blanked out

Edited December 1, 2013 by onno1000 (see edit history)

[onno1000]

http://home.kpn.nl/lo1li3/preview.jpg

Edited December 1, 2013 by onno1000 (see edit history)

[onno1000]

I tried combining your nice code with mine to get the picture links too. it worked. chech it out here below.

I'm very sorry i don't know how to paste the text format here and neither can i get a txt file uploaded here.

http://home.kpn.nl/lo1li3/export_all_with_image_urls.jpg

Maybe someone can help me out with that?

Greets

[rogasgr]

i just OCR'ed this image 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price,p.id_tax_rules_group, p.wholesale_price, p.reference,p.supplier_reference,p.id_supplier, p.id_manufacturer, p.upc,p.ecotax, p.weight, p.quantity, pl.description_short,pl.description, concat('http://',ifnull(conf.value,'example.com'),'/img/p/', p.id_product, '-' , pl.id_image, '.jpg') as product_image, pl.meta_title, pl.meta_keywords,pl.meta_description,pl.link_rewrite, pl.available_now,pl.available_later, p.available_for_order, p.date_add,p.show_price, p.online_only, p.condition, p.id_shop_default FROM sf2_product p LEFT JOIN sf2_image pi on p.id_product = pi.id_product LEFT JOIN sf2_product_lang pl ON (p.id_product = pl.id_product) LEFT JOIN sf2_category_product cp ON (p.id_product = cp.id_product) LEFT JOIN sf2_category_lang cl ON (cp.id_category = cl.id_category) LEFT JOIN sf2_category c ON (cp.id_category = c.id_category) LEFT JOIN sf2_product_tag pt ON (p.id_product = pt.id_product) LEFT JOIN sf2_configuration conf on conf.name = 'PS_SHOP_DOMAIN' WHERE pl.id_lang = 5 AND cl.id_lang = 5 AND p.id_shop_default = 1 AND c.id_shop_default = 1 GROUP BY p.id_product

Edited June 30, 2015 by rogasgr (see edit history)

[irrelevant]

This is the code I use for the image URL -

 

CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL",

 

 

Relevant tables are

LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1


LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)

LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)