category name query

[andreiven]

So I am trying to extract a csv file with every product id and category name (not id) only.

I am new to prestashop (and sql for that matter) and I was wondering if anyone could help me write a query for this purpose: product id and category name for each product id.

I found the  _category_lang  file where are located the columns I am intersested in: link_rewrite (eg. wholesale-clothing) and meta_title (eg. Wholesaleclothing » Wholesale Cheap Clothes » etc.).

These are the two columns that I need as the category name columns.

Edited November 3, 2016 by andreiven (see edit history)

[razaro]

Try this, in back office go to Advanced Parametars > SQL Manager.

Add new SQL query, add  name and following code

SELECT p.id_product as ID, cl.name as CategoryName
FROM ps_product p 
LEFT JOIN  ps_category_lang  cl ON p.id_category_default = cl.id_category
WHERE cl.id_lang = 1

Asnd save. You can export or veiw query after.

Note this will have two columns, product id and categor name, and that name will be for language with id 1.

So change that if language you want to export have other id.

 

Also similar SQL query you can add in this free module 

https://www.prestashop.com/forums/topic/506060-big-data-the-datas-csv-easy-for-all/

whith bit more options and display of data.

[andreiven]

thank you very much! this helped!