Query To Export Main Product Informations

[galleriaaurea]

I think you'll find useful
full exportation of the main informations of shop items

SELECT DISTINCT
a.`id_product`, 
sa.`active` AS `active` , 
b.`name` AS `name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as Categories,
a.`price` AS `price`,
a.`wholesale_price` AS `costs`, 
if (sale.reduction_type="percentage","",sale.reduction) as AMOUNT,
if (sale.reduction_type="percentage",100*sale.reduction,"") AS PERCENT,
reference,
m.name AS `manufacturer`, 
m2.name AS `supplier`, 
sav.`quantity` AS `quantity`,
b.description_short,
b.description,
GROUP_CONCAT(DISTINCT(concat( 'http://*YOUR_URL*/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' )) SEPARATOR ",")   AS url_image

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 2 AND b.`id_shop` = 1) 
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0 ) 
JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default) 
LEFT JOIN ps_manufacturer m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_supplier m2 ON (a.id_supplier = m2.id_supplier)
LEFT JOIN ps_image im ON a.id_product = im.id_product
left join ps_specific_price sale on a.id_product = sale.id_product
LEFT JOIN `ps_category_product` cat ON (a.`id_product` = cat.`id_product`)
LEFT JOIN ps_category_lang  cl on cat.id_category = cl.id_category
WHERE 1=1 
GROUP BY a.id_product
HAVING COUNT(DISTINCT a.id_product)

enjoy it

Edited January 31, 2016 by galleriaaurea (see edit history)

[Daresh]

Very nice query, can you expand it with querying also the shop's default lang (so it's not hard coded as 2) and shop's url (so it's not http://aurea.pswebshop.com)?

Edited January 31, 2016 by Daresh (see edit history)

[galleriaaurea]

Very nice query, can you expand it with querying also the shop's default lang (so it's not hard coded as 2) and shop's url (so it's not http://aurea.pswebshop.com)?

i forgot to remove my website's address... now it's written a generic *YOUR_URL*....

i've decided to publish this script only because i've spent 2 days to understand (and test) how this script could be written, and maybe i'm not the only one with the same problem...

you're right for that 2 hardcoded datas too.... let me find the solution and i'll change them too.

[galleriaaurea]

i'ld need to know which is the variable that shows/hides an item when its quantity is 0 (or less) and the variable with the message for absent items.
Is anyone able to help me please?