SQL Query Export Custom FEEDS

[ilisiaraul]

Hello! I'm trying to make a custom CSV export for a marketing affiliate platform.

 

I need the following custom sql , I've made one but shows's empty rows.

my website www.sensuals.ro

Awesome platform Prestashop,

 

"Product Name", "Description", "Short description (optional)", "price", "category", "subcategory (optional)", "url", "url picture", "product id", "generate link text (0) "" manufactuer (optional) "" in stock (0 or 1) "," other data in JSON or YAML format (optional) "

 

 

I am far but working on  

 

 

 

Thank you!

Edited November 15, 2014 by ilisiaraul (see edit history)

[NemoPS]

What do you mean by "generate link text (0)"?

As for the rest, can you show us your current query? Are you trying to get that using the back office sql manager? If so, you won't ever be able to export images ( see more info about it here http://nemops.com/prestashop-export-products/ ) or urls as they are got through php

[ilisiaraul]

And "generate link text (0)" means empty text with 0 (  ,"0" ) 

Yes back office sql manager 1.6

Edited November 15, 2014 by ilisiaraul (see edit history)

[ilisiaraul]

I've managed to make a good one.

 

I cannot make the img link be in their place if i move it all come blank.

I need only categories and url and done.

Online only keep the 0 and backorder allowed empty space ""

 

Somebody? 

 

Here is: 

 

SELECT pl.name AS 'Name',

pl.description_short AS 'Short description',

pl.description AS 'Description',

p.price AS 'Price tax excl.',

p.id_product AS 'ID',

p.online_only AS 'Available online only',

pl.link_rewrite AS 'URL rewritten',

p.active AS 'Active (0/1)',

pl.available_later AS 'Text when backorder allowed',

concat( 'http://sensuals.ro/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' ) AS url_image

FROM ps_product p

INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product

LEFT JOIN ps_image im ON p.id_product = im.id_product

WHERE 1=1

and p.active = 1

Edited November 15, 2014 by ilisiaraul (see edit history)

[NemoPS]

The image one is pretty cool Though it's a bit hard to figure out the real path, what about trying to target the rewritten one? say, take an image (cover), identify the pattern, like if it uses the image legend or product name (link_rewrite), and create a link to that?

[ilisiaraul]

I Just need the description to appear and some modification for url be full. Somebody? 

SELECT pl.name, pl.description_short AS "Descriere", " " AS 'Descriere scurta', p.price AS 'Pret', GROUP_CONCAT(DISTINCT(cl.name)) AS 'Categorie',pl.link_rewrite AS 'URL',

concat( 'http://sensuals.ro/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' ) AS 'url_imagini', p.id_product AS 'ID produs', "" AS 'Generare link text',

" " AS 'Brand',

p.on_sale AS 'In stoc (0/1)',

" " AS 'alte date in format JSON sau YAML(optional)'

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)

LEFT JOIN ps_image im ON p.id_product = im.id_product

WHERE pl.id_lang = 1

AND cl.id_lang = 1

AND p.id_shop_default = 1

AND c.id_shop_default = 1

GROUP BY p.id_product

 

Edited November 19, 2014 by ilisiaraul (see edit history)

[ilisiaraul]

I have resolved with a module. It does exactly what i need, don't need to bother anymore with sql waste of time especial when you need more feeds urgent. Awesome module. 

[SirBob]

Hi ilisiaraul

 

Can you please tell me which module you found that fixed your problem and where I can get this module?

 

Is it free or paid module?

 

Many thanks!

[ilisiaraul]

Hello! Works perfect!

This one! Good luck 

http://codecanyon.net/item/export-catalog-in-csv-format/7222934

Hi ilisiaraul

 

Can you please tell me which module you found that fixed your problem and where I can get this module?

 

Is it free or paid module?

 

Many thanks!

Edited January 6, 2015 by ilisiaraul (see edit history)