Process for exporting all categories?

[immc]

I'm in the build & break stage of learning PrestaShop ver8.2 and have a working understanding of the type of category hierarchy I need for this project. The problem is the amount of time it takes to create these categories. 

How to Export Categories & Sub-categories
If I click Categories > Export I get a spreadsheet containing Name, Description, Position, Displayed of the 7 categories but not any of the sub-categories nested in each. 

I came across this query statement: 

SELECT * FROM tsf_category, tsf_category_lang WHERE tsf_category.id_category = tsf_category_lang.id_category;

I changed the table prefix, select the category table from the list of MySQL Tables and try to save the new SQL query but an unexpected error is thrown.

Anyone have this process documented and tested on ver 8.2? I need this .csv available to import into rebuilds.

Any responses appreciated.

[musicmaster]

Your post is long but hard to understand.

The ps_category table contains everything you need about its position in the hierarchy. The most important field is id_parent that points to the id_category of the parent category. Position gives the relative position versus its peers. Level_depth is strictly spoken not needed.

is_root signals that the category is the root of some shop. It is not the absolute root of the category tree: there is one level below that has as id_parent a zero. Note that in shops that originate from before version 1.5 - when Prestashop introduced multishop - this lowest level will have a relatively high id_category.

The field nleft and nright are also related to the position of the categories towards each other. But that belongs in the advanced studies department.

[Knowband Plugins]

  On 3/21/2025 at 5:18 AM, immc said:

SELECT * FROM tsf_category, tsf_category_lang WHERE tsf_category.id_category = tsf_category_lang.id_category;

 

Expand  

Whats your query here? What an unexpected error. The query seems fine if having sing language on the store. 

 

 

[immc]

Apologies, but as stated I'm new to self-hosting PrestaShop ver8.2 and trying to figure out the actual process to successfully export the 132 categories I've created to a spreadsheet. These are nested inside of seven (7) categories: 

category_1
- sub_cat_1
- sub_cat_2

category_2
- sub_cat_1
- sub_cat_2

category_3
- sub_cat_1
- sub_cat_2

etc...

Based on what I've discovered thus far I'm suppose to click Categories > Gear icon > Export to perform the export but it doesn't include the 132 categories I've created. What I get in this export are the seven (7) categories but not the sub-categories nested under them.

 

The documentation doesn't go into detail on this process:

https://docs.prestashop-project.org/v.8-documentation/user-guide/selling/managing-catalog/managing-categories#managingcategories-importingandexportingcategories

 

I came across this query while searching other forum threads: 

Thread: 

 

 

Query: SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

Pasting this into SQL query returns the following error: The "ps_category" table does not exist.

 

I modify this query by changing the ps_ prefix, which I changed when installing PrestaShop, to tsf_. So: 

SELECT * FROM tsf_category, tsf_category_lang WHERE tsf_category.id_category = tsf_category_lang.id_category;

 

I go to Advanced Parameters > Database which opens SQL Manager.
- Click Add new SQL query.
- Enter name for query.
- Paste in query: SELECT * FROM tsf_category, ps_category_lang WHERE tsf_category.id_category = tsf_category_lang.id_category;
- Click Save. 

Error: Oops... looks like an unexpected error occurred.

Click back to returns error: 

I enable debug and get the following error after attempting to run the query: 

Bad SQL Query.

 

I see List of MySQL Tables and can confirm that every table leads with tsf_ as opposed to ps_.

Again, I'm simply trying to figure out and document the process for future exports. 

 

Any assistance is appreciated.

 

 

[Knowband Plugins]

I have tried running the query at my end & it's working fine.

If your tables have a prefix i.e. tsf_, both tables should have the same prefix.  It seems you are still using ps_category_lang. 

SELECT * FROM tsf_category, tsf_category_lang WHERE tsf_category.id_category = tsf_category_lang.id_category;

[Mediacom87]

Hi,

SELECT
    c.id_category,
    c.id_parent,
    cl.name AS category_name,
    CONCAT_WS(' > ',
        cl_root.name,
        cl_lvl1.name,
        cl_lvl2.name,
        cl.name
    ) AS full_path,
    CASE
        WHEN c.id_parent = 2 THEN 1
        WHEN c_lvl1.id_parent = 2 THEN 2
        WHEN c_lvl2.id_parent = 2 THEN 3
        ELSE 0
    END AS niveau,
    c.active,
    c.date_add,
    c.date_upd
FROM
    ps_category c
-- Main category language
JOIN ps_category_lang cl ON cl.id_category = c.id_category AND cl.id_lang = 1

-- Joint level 1 (parent)
LEFT JOIN ps_category c_lvl1 ON c_lvl1.id_category = c.id_parent
LEFT JOIN ps_category_lang cl_lvl1 ON cl_lvl1.id_category = c_lvl1.id_category AND cl_lvl1.id_lang = 1

-- Joint level 2 (grandparent)
LEFT JOIN ps_category c_lvl2 ON c_lvl2.id_category = c_lvl1.id_parent
LEFT JOIN ps_category_lang cl_lvl2 ON cl_lvl2.id_category = c_lvl2.id_category AND cl_lvl2.id_lang = 1

-- Level 3 join (real root, usually “Home”)
LEFT JOIN ps_category_lang cl_root ON cl_root.id_category = 2 AND cl_root.id_lang = 1

WHERE
    c.id_category NOT IN (1, 2)  -- Exclude root and Home if you want
ORDER BY
    full_path;

Export :
    1.    Execute this query in phpMyAdmin, click on “Export” > “CSV”.
    2.    Open in Excel: you'll get the full_path column, perfect for visually retrieving the structure.

[immc]

@Knowband Plugins If I copy and paste this query as is into Advanced Parameters > Database > SQL Manager and click Save it I get an error: 

SELECT
  *
FROM
  tsf_category,
  tsf_category_lang
WHERE
  tsf_category.id_category = tsf_category_lang.id_category;

Error:
Argument #1 ($string) must be of type string, array given
[TypeError 0]

Adding this into a SQL validator doesn't return an issue with the statement. 

 

@Mediacom87  After changing the prefix from ps_ to tsf_ I was able to use this export the csv via phpMyAdmin. Thanks!

 

The ideal scenario would be to use the Prestashop sample csv to import this data into a staging environment, make changes, and then import that updated csv back into the Prestashop live site. I unfortunately can't make sense of the format in this Prestashop sample categories csv versus the csv exported from phpMyAdmin.

[immc]

Just noticed that if I click Categories > Gear > Export and then attempt to import the few categories that are exported into my stage site the meta data isn't included and the category title is wrong. So I basically have to manually enter every category. 

 

[Knowband Plugins]

  On 3/25/2025 at 8:23 PM, immc said:

Adding this into a SQL validator doesn't return an issue with the statement. 

 

Expand  

Use this in the SQL Manager. 

SELECT * FROM  tsf_category c INNER JOIN  tsf_category_lang l ON c.id_category = l.id_category

 

 

[ps8modules]

Hi.

It's always going on and on. Why use a wheel when I can use a polygon. There are many examples here for exporting categories with a simple PHP script. You have to pointlessly add dozens of unnecessary things to SQL and then there's a topic for a month and nothing gets resolved. Prestashop doesn't have an ideal environment (visual) for doing anything logically. I gave a person here in the forums at least 19 examples of PHP scripts. Just enter what you need in the forum search.

Do you need a custom export script? No problem! Creating such a script is a matter of a few minutes of work!!!

Script for export categories to Excel XLS file:

export-categories.zipFetching info...

Just upload the file to the main Prestashop folder and call it in the browser. For example: https://my-site.com/export-categories.php

Edited Wednesday at 07:35 PM by ps8modules
added script (see edit history)