Change Category of Multiple Products

[SRD75]

Is there a way to change the category of multiple products at once, or do I have to individually edit all 200 products in a category?

[owas]

Possibly,
try installing this module - http://www.prestashopfreemodules.com/product-export-module/
export all your products in to a csv file - amend the category column and then use the import tool, not sure if this would work, but dont see why not, let me know if you need help.

[owas]

Possibly,
try installing this module
export all your products in to a csv file - amend the category column and then use the import tool, not sure if this would work, but dont see why not, let me know if you need help.

[SRD75]

I ended up using an SQL query in PHPMyAdmin. More simple than I thought. Thanks for your reply.

[JohnADK]

Would you mind sharing your sql query? I'm trying to get the right combination, but not having success. I only get some of the correct data over. Do you have a list of all the tables that need to be included for categories and products to get exported/imported correctly?

[SRD75]

Sure. I found out the ID numbers of the old category, and the new category products were to be moved into. You can find this by hovering your mouse over the category hyperlink in Prestashop admin. Then run the following SQL in PHPMyAdmin:

 

update ps_category_product set id_category = replace(id_category, 'Old ID', 'New ID');

ps_category_product is a table which contains matching category IDs and products IDs.

[tin22ooo]

Sure. I found out the ID numbers of the old category, and the new category products were to be moved into. You can find this by hovering your mouse over the category hyperlink in Prestashop admin. Then run the following SQL in PHPMyAdmin:

 

 

ps_category_product is a table which contains matching category IDs and products IDs.

Thanks a lot man !!! u save a lot of work, and time. My import had a faulty row, and i ended up with some messed up category/products.

[SteFer]

INSERT INTO ps_category_product

(id_category, id_product, position)

 

select 38, id_product, id_product

from ps_category_product as c

where

id_category in (30,31,32,33,34,35,36);

 

 

where 38 is category you want to add to a product

and (30,31,32,33,34,35,36, etc..) are the categories where actually products are!

[Zoltán Oláh]

Or - without coding - just try this module, if you have hundreds or thousands of products:

http://progikon.hu/prestashop_masscategorizer

[Ganesh Hipparkar]

INSERT INTO ps_category_product

(id_category, id_product, position)

 

select 38, id_product, id_product

from ps_category_product as c

where

id_category in (30,31,32,33,34,35,36);

 

 

where 38 is category you want to add to a product

and (30,31,32,33,34,35,36, etc..) are the categories where actually products are!

Thanks very much SteFer you made my day ..

[connectwide]

Can you please let me know where we need to run update query in prestashop to change the category id. As I can see only select query is allowed in database section under advance parameter. I need to update category id of around 2000 products at a time. Please help me on this and let me know we can run multiple update queries at a time.

[DevWL]

If you trying to move all products from one category to another, then you can try to use something like that

/* 
* author: Wiktor Liszkiewicz
* [email protected]
* test at: https://paiza.io/projects/Rn1YBOVx4JkDo7YHu2xVtg?language=mysql
* This scripts moves products from one category to another and also clenup the source category-product entry if the data is already asigned to destination category..
*/

-- create table as in PrestaShop (clone) from https://github.com/PrestaShop/PrestaShop/blob/1.7.7.x/install-dev/data/db_structure.sql
CREATE TABLE PREFIX_category_product (
  id_category int(10) unsigned NOT NULL,
  id_product int(10) unsigned NOT NULL,
  position int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (id_category, id_product),
  INDEX (id_product),
  INDEX (id_category, position)
);

-- insert demo data set I
INSERT INTO PREFIX_category_product (id_category, id_product, position)
VALUES
(0, 5416, 1),
(1, 5416, 1),
(2, 5416, 1),
(3, 5416, 1),
(0, 5418, 2),
(1, 5418, 2),
(2, 5418, 2),
(3, 5418, 2);

-- insert demo data set II
INSERT INTO PREFIX_category_product (id_category, id_product, position)
VALUES
(4, 2000, 1),
(5, 2000, 1),
(4, 5000, 2),
(5, 5000, 2);

-- check table data
SELECT * FROM PREFIX_category_product;

-- enter products id manualy or fint them wi query below
-- define category
SET @OLDCAT := 4;
SET @NEWCAT := 0;

-- set @STARTINGPOS position starting number will be 0 + 1 
SET @STARTINGPOS := 0;
-- or set @STARTINGPOS to the highest position value + 1 if category already have products 
select @STARTINGPOS := MAX(position) from PREFIX_category_product where id_category = @NEWCAT;
-- select @STARTINGPOS; -- debug

START TRANSACTION; -- start transation

-- update category where id_product is in old category and is not in new category (cleen up later with DELETE query)
UPDATE PREFIX_category_product 
    SET id_category = @NEWCAT,
    position = (select @STARTINGPOS := @STARTINGPOS + 1)
    WHERE id_category = @OLDCAT 
    AND id_product IN 
    (SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t1)
    and id_product NOT IN 
    (SELECT * FROM (SELECT DISTINCT  id_product FROM PREFIX_category_product WHERE id_category = @NEWCAT) AS t2);

-- remove products and category set if not moved because they are already assigned to destination category
DELETE FROM PREFIX_category_product WHERE id_product IN
    (SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t3)
    AND id_category = @OLDCAT;
    

COMMIT; -- end transation
    
SELECT * FROM PREFIX_category_product;

This would output the fallowing: 

The only thing you care about:

Also make sure to change table prefi:

to the one that you actually use (most likely it will be "ps_").

If you interested in running query on production then be sure to make a backup before attempting to do anything. FInd out your category sorce and destination ID and replace all "PREFIX_" in the query below with your own prefix (usually "ps_"). If you database is safe and you make everything ready then you can run the SQL query below. You could use phpMyAdmin or MySQL Workbench.

SET @OLDCAT := 4;
SET @NEWCAT := 0;

-- set @STARTINGPOS position starting number will be 0 + 1 
SET @STARTINGPOS := 0;
-- or set @STARTINGPOS to the highest position value + 1 if category already have products 
select @STARTINGPOS := MAX(position) from PREFIX_category_product where id_category = @NEWCAT;
-- select @STARTINGPOS; -- debug

START TRANSACTION; -- start transation

-- update category where id_product is in old category and is not in new category (cleen up later with DELETE query)
UPDATE PREFIX_category_product 
    SET id_category = @NEWCAT,
    position = (select @STARTINGPOS := @STARTINGPOS + 1)
    WHERE id_category = @OLDCAT 
    AND id_product IN 
    (SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t1)
    and id_product NOT IN 
    (SELECT * FROM (SELECT DISTINCT  id_product FROM PREFIX_category_product WHERE id_category = @NEWCAT) AS t2);

-- remove products and category set if already moved or exsists in new category table
DELETE FROM PREFIX_category_product WHERE id_product IN
    (SELECT * FROM (SELECT DISTINCT id_product FROM PREFIX_category_product WHERE id_category = @OLDCAT) AS t3)
    AND id_category = @OLDCAT;
    

COMMIT; -- end transation

 

 

Edited June 10, 2021 by DevWL
smal fix (see edit history)