whiteletters in blankpapers whiteletters in blankpapers - 1 year ago 197
SQL Question

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

I observed that the count of all products in my website is not equal to the number of products in category

. It is due to the fact that this biggest parent category was not always selected during product adding. So some products belong to
but others not.

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

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

Your usual help is appreciated. Thank you.

I found that the table in database is
. 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.

Answer Source

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