support

Author Topic: Bulk move products to different category  (Read 3435 times)

Offline Old-Paul

  • Newbie
  • *
  • Posts: 9
  • Karma: +1/-0
  • Old web guy. Doing this for over 28 years!
    • View Profile
    • Stuff Done
Bulk move products to different category
« on: December 11, 2023, 08:14:23 PM »
I goofed somewhere.  Happily products still there just not assigned to a category.

How can I select all and assign them in bulk to a category?

I guess another way to describe what I need would be "how can I move bulk products to different category?"

Thanks !
« Last Edit: December 11, 2023, 08:17:48 PM by Old-Paul »
=========================================
  What does "retired" mean?  I'm almost 78 still doing this crap!

Offline OneMore

  • Newbie
  • *
  • Posts: 46
  • Karma: +13/-0
    • View Profile
Re: Bulk move products to different category
« Reply #1 on: December 13, 2023, 06:12:40 AM »
Hello!

From what I see, this feature doesn't seem implemented yet, but it is easy to do such batch edition by creating a View.
In phpMyAdmin (which is almost for sure available on your localhost or web hosting), just paste the following MySQL query in the SQL tab:

CREATE VIEW batchEditProductCategories AS (SELECT `t`.`product_id` AS `product_id`,`t`.`category_id` AS `category_id`,`p`.`model`,`p`.`sku` FROM (`abantecart1211`.`abc_products_to_categories` `t` JOIN `abantecart1211`.`abc_products` `p` ON (`t`.`product_id`=`p`.`product_id`)));

In above code, 'abantecart1211' is the name of my database and 'abc_' is the name of the table prefix. Adapt it to the specifics of your installation.
Specifying the name of the database is optional.

Once the view created, you can perform operations on it exactly like if it was a table.
This means that in phpMyAdmin, you can simply double-click table cells to edit their values, and that you can as well apply SQL queries.

Listing only those products with an empty category:
SELECT * FROM batchEditProductCategories WHERE category_id='';
Note that the WHERE condition on empty categories could also habe been applied to the query that created the view.


Batch updating records with an SQL query
To give you and idea about how powerful SQL can be, this unlikely example sets to 82 the category_id of those products where the sku starts with "P", ends with "56", and only if the current category_id is 23 or 34:
UPDATE batchEditProductCategories SET `category_id`=82 WHERE ((`category_id`=23 OR `category_id`=34) AND `sku` LIKE 'P%56');

Such edit will reflect is the tables to which the view is pointing to.

To enrich the view created above, you can get the category names from the `category_descriptions` table.
The product names can also be obtained from the `products_description`table, either as alternative or as complement to the sku and model fields from the `products` table. This is of course performed by joining tables using "JOIN ... ON" in your query.
If your

If your site is multilingual, and your view is getting names from the`category_names` and `product_names` tables, you will need to add "... WHERE language_id=1" (or alike) in your query, to avoid duplicates coming from the multilingual categories and product names.

A very good way for you to learn SQL by doing.

Hope this helps.  ;)
« Last Edit: December 13, 2023, 06:17:34 AM by OneMore »

 

Powered by SMFPacks Social Login Mod