MYSQL, Prestashop, how to loop over all id_products?

Question

I want to insert pairs of: category_number (1184) / id_product in the ps_category_product table.

To add one single pair (for instance 1184/2, with 1184 being the category number and 2 the product ID), the following request works:

INSERT IGNORE INTO ps_category_product SELECT 1184, 1, 0 FROM ps_product;

But I would like make this request over all products, so I tried this request:

INSERT IGNORE INTO ps_category_product SELECT 1184, (SELECT id_product from ps_product), 0 FROM ps_product;

But it does not work.

Any idea ?

Thanks

Answer 1

You don't need the subquery

create Table ps_category_product (id int, id_product int, id2 int)

CREATE TABLe ps_product (id_product int)

INSERT INTO ps_product VALUES (1),(2),(4)

INSERT IGNORE INTO ps_category_product SELECT 1184, id_product , 0 FROM ps_product;

SELECT * FROM ps_category_product

  id | id_product | id2
---: | ---------: | --:
1184 |          1 |   0
1184 |          2 |   0
1184 |          4 |   0

db<>fiddle here