Export csv of all the categories

[marie12]

Hello,
I need to export all the categories, included their name and link_rexrite.
 

I tried the solution given here :

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

But infortunately, some columns are empty : active, name, description... all the values without the alias "a.".

But this is specially the ones I need !

 

I tried to rewrite this code to obtain the data in my csv files.

Il tried to write :

FROM `ps_category_lang` a

because it's in this table we find the data I need (and I changed some other things in this code...)

I tried to use UNION to have data from the 2 tables `ps_category_lang` and `ps_category`

...but without success...

because I'm not an SQL expert...

 

I need the list of all the categories with the name, the id and the link_rewrite, to delete many of them, create some new one, and re-organize the tree of categories.

 

So somenone can help to complete this code (that is an interesting code for beginning).

 

Regards

Edited January 17, 2017 by marie12 (see edit history)

[w3bsolutions]

The SQL looks correct, the field without the "a." are retrieved from the "ps_category_lang" table. From your problem it seems like what is wrong is the language id and/or shop id in:

AND b.`id_lang` = 5 AND b.`id_shop` = 5

and:

= sa.`id_category` AND sa.id_shop = 5

Are you sure 5 is the correct id for the language and the shop? If you are using default single-shop installation, the shop id should be 1. You can check the language id in Localization > Languages, id column. 

 

Let us know if you manage to get the correct result. 

[marie12]

Tank you for the reply.

 

Your were right :

I found the number of the shop, not in the back-office, but in the database, and it was 1.

For the language, it was 2.

 

So the request is now :

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `link_rewrite`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 2 AND b.`id_shop` = 1)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 1)  
WHERE 1


ORDER BY a.`id_category` ASC

And it works, it gives me exactly that I need. All the columns are filled.

Thank you for the help !

[w3bsolutions]

You are welcome.

 

Don't forget to mark the question as Solved (edit first post and add [sOLVED] at the beginning of title). 

[awrobl]

Old post but solution works great , for PS 8.1 🙂

thx.