CODEWITHSUNDEEP
phpsqlprestashopprestashop-1.6
Adib Aroui
Adib Aroui

Reputation: 5067

Mass product category update (adding `home` category to all products) - Prestashop 1.6.0.6

I observed that the count of all products is not equal to the number of products in category home. It is due to the fact that this biggest parent category was not always selected during product adding. So some products belong to home but others not.

While I need to display the correct number of products for the category home page, I was looking in the database for the columns that holds the categories but not found them in ps_product or ps_product_lang.

what is the idea behind an sql query that can mass modify the appropriate fields in database so that all my products belong to category home.

Your usual help is appreciated. Thank you.

Edit: I found that the table in database is ps_category_product. Is it the only one?

this table is like this:

+-------------+------------+----------+
| id_category | id_product | position |
+-------------+------------+----------+
|          11 |          1 |        1 |
|          11 |          2 |        2 |
|          11 |         10 |        3 |
+-------------+------------+----------+

I need maybe to create x lines (x= the number of products) . each line has id_category=1 and id_product= product id and position= position++. Is is the best approach?

Any help regarding how to implement it in sql?

Thanks again.

Upvotes: 2

Views: 7137

Answers (1)

yenshirak
yenshirak

Reputation: 3106

You can assign every product to the Home category with a simple SQL query:

INSERT IGNORE INTO ps_category_product SELECT 2, id_product, 0 FROM ps_product

2 is normally the id_category value of the Home category, 0 is the position.

If you want unique position values then you need a more complex query:

INSERT IGNORE INTO ps_category_product SELECT 2, id_product, ((SELECT MAX(position) FROM ps_category_product WHERE id_category = 2) + (@inc := @inc + 1)) FROM ps_product INNER JOIN (SELECT @inc := 0) AS i

Upvotes: 5

Related Questions