Jump to content
Asep Komarudin

sql query for export all images url

Recommended Posts

Hi,, anyone can help me with sql query?
I have a query for export products with combination,, but I only get 1 images for 1 combination item.
example if I have 1 products with 2 combination and 3 images in each combination,, this query only give me 2 images url.
how can I get all images url?
 
*sorry for my english, hope someone can help me,, thanks..
 

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
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"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
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)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

Share this post


Link to post
Share on other sites

No answer for this question :(

well,, I'm done with this problem..

Maybe someone need it, I'll share a query.

 

Thanks.

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_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"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
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)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product
  • Like 3

Share this post


Link to post
Share on other sites

Hello, sorry for the trouble, the code that inserted in the second post is the correct one, right. This code entered directly into SQL Manager and then into SQL Query? Thank you very much for your help, unfortunately I'm not very experienced

2962250C-B445-4E82-8CB9-6A851789ED7C.jpeg

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More