SQL export

[MihaiAlex]

Hello,

 

I need some help with a export from database. I want to have in  a CSV this fields:

 

-product name

-description

-price

-category

-url

-image url

-product id

-in stock ( 0 or 1)

-link generation (0 or 1)

 

This CSV will be used for a affiliate website.

[musicmaster]

import the official csv into Excel or some other spreadsheet and remove the fields that you don't need.

[MihaiAlex]

Hi.I have create a script in SQL:

 

SELECT 

pl.name AS 'Nume produs',

pl.description AS 'Descriere',

pl.description_short AS 'Mesaj scurt',

p.price AS 'Pret',

p.id_category_default AS 'Categorie',

"" AS 'Subcategorie',

pl.link_rewrite AS 'URL',

concat( 'http://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' ) 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

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

AND pl.id_lang = 4 

 

 

 

p.id_category_default AS 'Categorie',  This field get only ID of category and I need the name of category. Some ideeas for this problem?

[musicmaster]

The name is in the category_lang table. So you will get something like

 LEFT JOIN ". _DB_PREFIX_."category_lang l on l.id_lang='.$id_lang' AND c.id_category=l.id_category AND l.id_shop='".$id_shop."'  

added to your query.

[MihaiAlex]

Unknown column 'c.id_category' in 'on clause'

 

 

Is not working. Any advice?

[musicmaster]

Please study the suggested line and do not blindly enter it. I assume you know something about sql.