[SOLVED] Prestashop query MYSQL for category

[ietax]

I need correct query for export parent category on prestashop.

On Prestashop there are 2 tables.

ps_category_lang - id_category - name

ps_category - id_category - id_parent

id_parent is category number of upper category i'd like to associate name of upper category to correct id_parent.

I hope to be clear. thanks.

Edited September 19, 2017 by ietax (see edit history)

[Scully]

Not yet clear, make an example as text output.

[ietax]

Ok, i will try

 

SELECT ps_category.id_category, ps_category_lang.name, ps_category.id_parent FROM ps_category LEFT JOIN ps_category_lang ON (ps_category.id_category = ps_category_lang.id_category) WHERE ps_category_lang.id_lang = 3

 

 

 

id_category     name           id_parent      

1                      Root                      0                  

2                       Home                   1           

 

i'd like to have one more column with name ok id_parent 

 

es. 

 

id_category     name           id_parent           name_parent

1                      Root                      0                  

2                       Home                   1                      Root

Edited September 15, 2017 by ietax (see edit history)

[Scully]

Here we go:

SELECT c.id_category, c.id_parent, cl.name cat_name, clp.name parent_name
FROM ps_category c
LEFT JOIN ps_category_lang cl    ON cl.id_category = c.id_category AND cl.id_lang = 1
LEFT JOIN ps_category cp         ON cp.id_category = c.id_parent
LEFT JOIN ps_category_lang clp    ON clp.id_category = cp.id_category AND clp.id_lang = 1

If you use more than one language, you can use id_lang = 1 (where 1 is your desired language id) to limit to one language only.

You can delete these conditions and you'll get the output for all languages.

[ietax]

Perfect, thanks a lot!!!

[Scully]

You are welcome. You might mark the topic as solved (add SOLVED to the title) and / or mark the best answer.

Regards, Sully